Oh, thank you so much for such an elaborate reply! The "groupby method"
totally suits me.
On Sunday, April 5, 2015 at 6:17:35 PM UTC+3, Michael Bayer wrote:
>
>
>
> On 4/5/15 8:46 AM, Yegor Roganov wrote:
>
> Given the User-Addresses schema as in the docs, I need to select all users
> along with only those email addresses which end with "@gmail.com".
> The question is how do I group these filtered emails by user?
> Apparently I can use `contains_eager` to write something like this:
>
>
> session.query(User).outerjoin(User.addresses).filter(Address.email_address.like('%@
> gmail.com'))\
> .options(contains_eager(User.addresses))
>
> But having the filtered email addresses on the `addresses` attribute is
> rather confusing.
>
>
> I agree, this is not the typical case you'd use it.
>
>
> I'd like to either:
> 1. iterate a query results in a clear way:
> for user, gmail_addrs in session.query(???)
>
> sure, just say session.query(User, Address).outerjoin(...) and that's what
> you get. But this is a product result; if User "A" has three addresses,
> you get User "A" back three times, one for each address. This is just
> plain SQL behavior.
>
>
> 2. get the filtered children attached to a different field (smth like
> `contains_eager(User.addresses, attach_to='_gmail_addrs')`)
>
>
> right, a "nested" result but not attached to the User.addresses
> attribute. There was a database that did this, and I even wrote a
> SQLAlchemy ORM driver for it, but unfortunately it's called FoundationDB
> and it doesn't seem to exist any more :).
>
> There isn't a specific option on contains_eager() like that because the
> mechanics of mapped attributes on mapped classes is not really
> "lightweight" enough for that operation to be appropriate in that way; it
> could be possible, that something like contains_eager() loading could apply
> things to simple instance-only collections that aren't relationships, but
> this isn't something built in right now.
>
> However, if you want to group the User, Address objects, use plain
> Python. In this case itertools.groupby will give you what you need as a
> one liner, assuming the User rows for a given identifier are contiguous
> (which is always the case for simple joins, or an order_by() guarantees it):
>
> import itertools
> q = session.query(User,
> Address).outerjoin(User.addresses).filter(Address.email.like('%@gmail.com
> ')).order_by(User.id)
>
> for user, collection in itertools.groupby(q, key=lambda row: row[0]):
> addresses = (row[1] for row in collection if row is not None)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks.
> --
> 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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
On Sunday, April 5, 2015 at 6:17:35 PM UTC+3, Michael Bayer wrote:
>
>
>
> On 4/5/15 8:46 AM, Yegor Roganov wrote:
>
> Given the User-Addresses schema as in the docs, I need to select all users
> along with only those email addresses which end with "@gmail.com".
> The question is how do I group these filtered emails by user?
> Apparently I can use `contains_eager` to write something like this:
>
>
> session.query(User).outerjoin(User.addresses).filter(Address.email_address.like('%@
> gmail.com'))\
> .options(contains_eager(User.addresses))
>
> But having the filtered email addresses on the `addresses` attribute is
> rather confusing.
>
>
> I agree, this is not the typical case you'd use it.
>
>
> I'd like to either:
> 1. iterate a query results in a clear way:
> for user, gmail_addrs in session.query(???)
>
> sure, just say session.query(User, Address).outerjoin(...) and that's what
> you get. But this is a product result; if User "A" has three addresses,
> you get User "A" back three times, one for each address. This is just
> plain SQL behavior.
>
>
> 2. get the filtered children attached to a different field (smth like
> `contains_eager(User.addresses, attach_to='_gmail_addrs')`)
>
>
> right, a "nested" result but not attached to the User.addresses
> attribute. There was a database that did this, and I even wrote a
> SQLAlchemy ORM driver for it, but unfortunately it's called FoundationDB
> and it doesn't seem to exist any more :).
>
> There isn't a specific option on contains_eager() like that because the
> mechanics of mapped attributes on mapped classes is not really
> "lightweight" enough for that operation to be appropriate in that way; it
> could be possible, that something like contains_eager() loading could apply
> things to simple instance-only collections that aren't relationships, but
> this isn't something built in right now.
>
> However, if you want to group the User, Address objects, use plain
> Python. In this case itertools.groupby will give you what you need as a
> one liner, assuming the User rows for a given identifier are contiguous
> (which is always the case for simple joins, or an order_by() guarantees it):
>
> import itertools
> q = session.query(User,
> Address).outerjoin(User.addresses).filter(Address.email.like('%@gmail.com
> ')).order_by(User.id)
>
> for user, collection in itertools.groupby(q, key=lambda row: row[0]):
> addresses = (row[1] for row in collection if row is not None)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks.
> --
> 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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
On Sunday, April 5, 2015 at 6:17:35 PM UTC+3, Michael Bayer wrote:
>
>
>
> On 4/5/15 8:46 AM, Yegor Roganov wrote:
>
> Given the User-Addresses schema as in the docs, I need to select all users
> along with only those email addresses which end with "@gmail.com".
> The question is how do I group these filtered emails by user?
> Apparently I can use `contains_eager` to write something like this:
>
>
> session.query(User).outerjoin(User.addresses).filter(Address.email_address.like('%@
> gmail.com'))\
> .options(contains_eager(User.addresses))
>
> But having the filtered email addresses on the `addresses` attribute is
> rather confusing.
>
>
> I agree, this is not the typical case you'd use it.
>
>
> I'd like to either:
> 1. iterate a query results in a clear way:
> for user, gmail_addrs in session.query(???)
>
> sure, just say session.query(User, Address).outerjoin(...) and that's what
> you get. But this is a product result; if User "A" has three addresses,
> you get User "A" back three times, one for each address. This is just
> plain SQL behavior.
>
>
> 2. get the filtered children attached to a different field (smth like
> `contains_eager(User.addresses, attach_to='_gmail_addrs')`)
>
>
> right, a "nested" result but not attached to the User.addresses
> attribute. There was a database that did this, and I even wrote a
> SQLAlchemy ORM driver for it, but unfortunately it's called FoundationDB
> and it doesn't seem to exist any more :).
>
> There isn't a specific option on contains_eager() like that because the
> mechanics of mapped attributes on mapped classes is not really
> "lightweight" enough for that operation to be appropriate in that way; it
> could be possible, that something like contains_eager() loading could apply
> things to simple instance-only collections that aren't relationships, but
> this isn't something built in right now.
>
> However, if you want to group the User, Address objects, use plain
> Python. In this case itertools.groupby will give you what you need as a
> one liner, assuming the User rows for a given identifier are contiguous
> (which is always the case for simple joins, or an order_by() guarantees it):
>
> import itertools
> q = session.query(User,
> Address).outerjoin(User.addresses).filter(Address.email.like('%@gmail.com
> ')).order_by(User.id)
>
> for user, collection in itertools.groupby(q, key=lambda row: row[0]):
> addresses = (row[1] for row in collection if row is not None)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks.
> --
> 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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
