sqlalchemyfastapi

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


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.


Solution

  • 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>))