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