Doing a recursive sqlalchemy selectinload query for a many to many relationship

Question

I have a many to many self referential relationship where data looks like:

{
  "parent": {
    "child": {
      "nodes": [
        {
          "nodes": [
            {
              "attr": "value",
              "nodes": []
            }
          ]
        },
        {
          "nodes": [
            {
              "attr": "value",
              "nodes": []
            }
          ]
        }
      ]
    }
  }
}

So I do a query like this:

statement = select(Parent)
  .filter(Parent.id == parent_id)
  .options(selectinload(Parent.child).
           .selectinload(Child.nodes)
           .selectinload(Child.nodes)
           .selectinload(Child.nodes)
  )
result = await async_session.execute(statement)

parent = result.scalars().first()

And that will give me 3 levels deep of nodes in my json output. But is there a way to do that so it goes as many levels deep as needed until nodes = [] ? Do I need to use something like marshmallow-sqlalchemy ? Or is there an easy way to do this?

Thanks

Jon

SQLAlchemy 2.0.36 asyncpg 0.30.0 fastapi 0.112.0

Editing to add a possible workaround if there's a way to do this dynamically.

select_options=[
      selectinload(Parent.child)
      .selectinload(Node.nodes)
]
query = select(Parent)
    query = query.filter(Parent.id == parent_id)
    for option in select_options:
      query = query.options(option)

So the above allows me to dynamically add as many options to the query. But I can't figure out how to add as many suboptions to the query. I want to chain a bunch of selectinload(). How can I do this dynamically:

.options(
  selectinload(Parent.child)
    .selectinload(Child.nodes)
    .selectinload(Child.nodes)
    .selectinload(Child.nodes)
    .selectinload(Child.nodes)
    .selectinload(Child.nodes)
    .selectinload(Child.nodes)
)

Is there a way I can create a loop to add all those .selectinload() methods? The above works great because I can see nodes that are 6 levels deep. And if I only have 3 levels deep of nodes, it works too. So if I could just do a loop of adding 50 selectinloads, it will cover everything up to 50 levels deep which is more than enough for my purposes.

Answer 1

Not dynamic, but selectinload takes an argument recursion_depth, so this eliminates the need for a loop. You should be able to just do

statement = select(Parent)
  .filter(Parent.id == parent_id)
  .options(selectinload(Parent.selectinload(Child.nodes, recursion_depth=<depth>))

Answer 2

From another thread, I got this solution to do a recursive selectinload:

  child_select = selectinload(Parent.child)
  for _ in range(<depth>):
    child_select = child_select.selectinload(
      child_select.nodes)

  statement = select(Parent).filter(
    Parent.id == parent_id).options(child_select)
  result = await session.execute(statement)
  parent = result.scalars().first()