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+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
>
> <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/4e2aa150-a0c2-4895-9fb2-8f0e856bdad6%40www.fastmail.com.
