On Wed, May 22, 2019 at 1:02 AM Manoj Mokashi <[email protected]> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
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.
