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 ?
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.
