On Thu, May 23, 2019, at 8:04 AM, Manoj Mokashi wrote:
> >>Was aiming for DRY( don't repeat yourself) for the relation : once i
> >>declare the relation, i should not have to join to it again, to filter it.
DRY is about unnecessary repetition that makes it difficult to change parts of
the code later, because certain elements are repeated in many places. That's
not the case here as there are two distinct API operations being performed on
the object.
>
> To be clearer,
> The relationship could be loaded lazy or selectin, not necessarily a join.
> It should load as defined, but with the filter applied.
filters don't apply to lazy or selectin loads or any other kind of load other
than contains_eager() which is against the Query as it stands, so I'm not sure
what you mean.
>
> regards,
> manoj
>
> On Thu, May 23, 2019 at 10:41 AM Manoj Mokashi <manojmoka...@gmail.com> wrote:
>> Hi Michael,
>>
>> Thanks for the quick reply as usual.
>> You have understood the use case correctly.
>> Was aiming for DRY( don't repeat yourself) for the relation : once i declare
>> the relation, i should not have to join to it again, to filter it.
>> The transform looks useful.
>>
>> Another thing : Is it possible to specify which relation columns to fetch
>> when i declare the relation ?
>>
>> regards,
>> manoj
>>
>> On Wed, May 22, 2019 at 12:05 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
>>> On Wed, May 22, 2019 at 1:02 AM Manoj Mokashi <manojmoka...@gmail.com>
>>> wrote:
>>> >
>>> > Hi Michael,
>>> >
>>> > It would be nice to have something like options(filterrelated(relation,
>>> filter))
>>> > That way, since the join is already specified in the relation, we don't
>>> have to add it again.
>>> > Or is that difficult due to lazyloading ?
>>>
>>> hi there -
>>>
>>> you're responding to a message that is 11 years old. Can you start
>>> from scratch and specify what you are looking for?
>>>
>>> "filterelated" as you describe would appear to correspond to:
>>>
>>>
>>> query(Thing).outerjoin(Thing.relationship).filter(Thing.something ==
>>> 'bar').options(contains_eager(Thing.relationship))
>>>
>>> that is, you are asking for three separate things, so there's three
>>> separate methods to call. I don't see it as straightforward from an
>>> API perspective to add various shortcut functions that perform these
>>> tasks in a different way since this spreads confusion.
>>>
>>> If you'd like you make your own "filterrelated" function, do this:
>>>
>>> def filterrelated(relation, filter):
>>> def transform(q):
>>> return
>>> q.outerjoin(relation).filter(filter).options(contains_eager(relation))
>>> return transform
>>>
>>>
>>> then you use it like this:
>>>
>>> my_query = my_query.with_transformation(filterrelated(relation, filter))
>>>
>>> that way you can have the funciton you want and SQLAlchemy's official
>>> API doesn't have to have a confusing array of functions that all do
>>> similar yet different things.
>>>
>>>
>>>
>>>
>>> >
>>> > Btw, when we use contains_eager() with add_entity(), it creates two
>>> entities in the output instead of just the main one.
>>> > Is that expected ?
>>> >
>>> > regards,
>>> > manoj
>>> >
>>> > On Thursday, March 20, 2008 at 7:53:57 PM UTC+5:30, Michael Bayer wrote:
>>> >>
>>> >>
>>> >> On Mar 19, 2008, at 8:31 PM, Fotinakis wrote:
>>> >>
>>> >> >
>>> >> >
>>> >> > SELECT *
>>> >> > FROM users
>>> >> > LEFT OUTER JOIN
>>> >> > ( SELECT * FROM addresses WHERE type = 1 )
>>> >> > AS addresses ON users.id = addresses.uid
>>> >> >
>>> >> > I can do this:
>>> >> >
>>> >> > query =
>>> >> > session
>>> >> > .query
>>> >> > (User
>>> >> > ).add_entity
>>> >> > (Address).outerjoin(addresses).filter(Address.type=='home')
>>> >> >
>>> >> > But, that filters on the entire query, not just on the joined sub-
>>> >> > query, generating something like this:
>>> >> >
>>> >> > SELECT *
>>> >> > FROM users
>>> >> > LEFT OUTER JOIN addresses ON users.id = addresses.uid
>>> >> > WHERE addresses.type = 1
>>> >> > ORDER BY hosts.mac
>>> >> >
>>> >> > Because it's a one-to-many relationship, this query only returns the
>>> >> > users that have a home addresses ... and _excludes_ users totally who
>>> >> > have an address, but one that is not of type 'home'. I need it to
>>> >> > return all users regardless (hence the LEFT JOIN) and just join
>>> >> > addresses of type 1.
>>> >> >
>>> >>
>>> >> you'd probably want to put the criterion in the ON clause:
>>> >>
>>> >>
>>> >> session
>>> >> .query
>>> >> (User).add_entity(Address).select_from(users.outerjoin(addresses,
>>> >> and_(Address.type=='home', Address.user_id=User.id)))
>>> >>
>>> >> alternatively you can shove the actual subquery in there in a few
>>> >> ways, one of them is like this:
>>> >>
>>> >> sel = addresses.select().where(Address.type=='home')
>>> >> session.query(User).add_entity(Address).outerjoin(('addresses',
>>> >> sel))
>>> >>
>>> >> or otherwise spell out the join to the subquery using select_from()
>>> >> again.
>>> >>
>>> >>
>>> > --
>>> > 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
>>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
>>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/f10335a2-9b95-4f1c-881b-03202d697121%40googlegroups.com.
>>> > For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> 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 a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> sqlalchemy+unsubscr...@googlegroups.com
>>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXFmGu8vUR4%3D88vP%2BZ%3Dvi-bgOjPwCgx6r912XX_MF6-VOQ%40mail.gmail.com.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B8WZoFGF_8T7OnakiQm6F83zUY%2BwGFvURjjFmCzX-XQA%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B8WZoFGF_8T7OnakiQm6F83zUY%2BwGFvURjjFmCzX-XQA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/b20d6045-5a6d-4bbf-ab50-2e203a0d6950%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
