Let's say I have a model with a one to many relationship as such:
class A(Base):
id = ...
class B(Base):
id = ...
some_field = ....
a_id = Column(ForeignKey(A.id)...
a = relationship(A, backref=backref('bs', lazy='dynamic'))
I can define a method on A:
class A(Base):
...
def get_b_with_some_field(self, some_field):
return self.bs.filter(B.some_field==some_field)
to get all b's that have a certain value of `some_field`.
Is there any way to accomplish this with eager fetching to avoid the n+1
select problem that will occur if I want to query for a lot of `A`s and
then iterate over the collection and for each call `get_b_with_some_field`
with the same value of `some_field`?
One option is to to change relationship to from `lazy='dynamic'` to
`lazy='subquery'` or ``lazy='selectin'`` and then implement the filtering
in `get_b_with_some_field` in Python. This will address the n+1 select
problem, but will cause pulling extra data from the database (and extra
work in Python).
I thought `contains_eager`
<https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html?highlight=contains_eager#using-contains-eager-to-load-a-custom-filtered-collection-result>
might be relevant; however, I only see it being mentioned in the case of
joined loads.
The reason I am looking for this functionality is I am defining a graphql
API that looks like the following:
type query {
as: [A!!
}
type A {
...
bs(some_field: String): [B!]!
}
type B {
...
some_field: String!
}
where I would like to be able to specify a filter on the `bs` relationship
from `A`. I would ideally like to 1. avoid the n+1 select issue and 2.
perform the some_field filtering at the database level, and 3. leverage as
much of the ORM as possible ;-)
Is it possible to do this within SQLA?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/55c7b3a6-bfee-45b6-83bc-25185bf7af87n%40googlegroups.com.
