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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> 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.
