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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/55c7b3a6-bfee-45b6-83bc-25185bf7af87n%40googlegroups.com.