A.bs only goes to the "bs" collection on an A. there's no eagerloading that
puts the collection on some other arbitrary place.
On Wed, Sep 23, 2020, at 6:03 PM, agrot...@gmail.com wrote:
> Cool, yes I think that is what I am looking for. Is there any way to alias
> the relationship (as read only) to: 1. allow for multiple different filters
> of the same property and 2. make sure when I read the value (in another place
> in the code), I have confidence the filter was applied?
>
> something like (I made up the syntax):
> q = session.query(A).options(selectinload(A.bs).and_(B.some_field ==
> value).as(f'bs_filtered_by_{value}')
> ...
> for a in q:
> for b in a.bs_filtered_by_xxxx:
> ....
>
>
> On Wednesday, September 23, 2020 at 12:21:41 PM UTC-4 Mike Bayer wrote:
>>
>>
>> On Wed, Sep 23, 2020, at 4:17 PM, agrot...@gmail.com wrote:
>>> 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?
>>
>> I would still use a separate relationship on the same class, you can always
>> make a @hybrid_property that switches between the two relationships
>> depending on what you want to do.
>>
>> In version 1.4, which will be in betas as soon as I can get a huge amount of
>> new docs written, you will have a potentially better option for this which
>> is the PropComparator.and_() operator. you can play with this now from git
>> master if you were interested:
>>
>> https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#adding-criteria-to-loader-options
>>
>> that way you'd say:
>>
>> session.query(A).options(selectinload(A.bs).and_(B.some_field == value))
>>
>> that might be what you're waiting for here
>>
>>
>>>
>>>
>>> # 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 <http://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+...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/e20cd72d-796f-4e99-a91d-55f4252f5fd6n%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/e20cd72d-796f-4e99-a91d-55f4252f5fd6n%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/077fe551-e829-4da0-8a70-e65933ec3446n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/077fe551-e829-4da0-8a70-e65933ec3446n%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/5f5b01e1-47aa-422d-aff4-163a3cbbae02%40www.fastmail.com.
