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, [email protected] 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e20cd72d-796f-4e99-a91d-55f4252f5fd6n%40googlegroups.com.
