I actually don't really care that much to have the attribute remain dynamic. In fact there is only one *specific* filtering that I want to apply to it, but that filtering will vary from (web) request to (web) request. This is what made me think of using contains_eager.
Right now this is the best solution I have come up with, which is to define a temporary class that extends A and add to that class a new relationship with the custom filter applied. I then specify to selectinload that property. Is there a better way to do this? # Run this code for each web request, reading some_field_value from the value the client specifies in the request: some_field_value = ... class ATmp(A): bs_temp = relationship( lambda: models.B, primaryjoin=( (models.A.id == models.B.a_id) & (models.B.some_field == some_field_value) ), ) q = return db.session.query(ATmp).options(selectinload(cls.bs_temp)) # iterate over the q (which in iterable of "A"s) and for each A, iterate over the bs_temp, which is a filtered collection of Bs. This also leads to a warning: SAWarning: This declarative base already contains a class with the same class name and module name as my_app.graphql.queries.ATmp, and will be replaced in the string-lookup table though it does seem to work (I am able to avoid n+1 and do the filtering in the DB). On Wednesday, September 23, 2020 at 8:10:44 AM UTC-4 Mike Bayer wrote: > > > On Wed, Sep 23, 2020, at 5:43 AM, agrot...@gmail.com wrote: > > 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`?' > > > if you want to have that attribute remain on "dynamic" then you'd need to > define a second relationship where you can use normal eager fetching > strategies, and then use that for those cases. > > IMO "dynamic" is not really worth it, you can get the same queries more > programmatically by using query(B).filter(with_parent(some_a, A.bs)). > > > > > > > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/55c7b3a6-bfee-45b6-83bc-25185bf7af87n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/55c7b3a6-bfee-45b6-83bc-25185bf7af87n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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/e20cd72d-796f-4e99-a91d-55f4252f5fd6n%40googlegroups.com.