On Tue, Nov 10, 2009 at 9:55 PM, jcm <jonmast...@gmail.com> wrote:
>
> Folks,
>
> I could do with some decent docs on subqueries. I've tried to play
> with what's on the website, but it's not helping me to convert the
> following into an SQLAlchemy subquery:
>
> select * from symbollistentries WHERE
> symbollistentries.symbollistentry_id NOT IN (select
> symbollistentries.symbollistentry_id from symbollistentries join votes
> ON (votes.symbollistentry_id=symbollistentries.symbollistentry_id)
> join states ON (states.state_id=votes.state_id) join users ON
> (users.user_id=states.owner_id) WHERE users.user_name='admin');
>
> It doesn't really matter what I'm doing here, this is just an example,
> and I've all kinds of mappers in place that I use for queries. What I
> really need is a pointer to some good subquery examples, not the
> solution to encoding the above. I need to understand how aliases work
> and how I can, for example, perform the nested select, alias it and
> then test for "NOT IN" using another query. I can't see how you
> implement "NOT IN" either :)
>
>
Easy part is the "NOT IN" question. the not_() function or ~ operator will
negate an expression.
tbl.c.colum.in_([1,2,3]) -> tbl.c IN (1,2,3)
not_( tbl.c.colum.in_([1,2,3]) ) -> tbl.c NOT IN (1,2,3)
~tbl.c.colum.in_([1,2,3]) -> tbl.c NOT IN (1,2,3)
I can describe you how I deal with subqueries. Since you say you have
mappers, I assume you are using ORM syntax like I do.
Step 1. Create and test a regular query that gives the result you are
looking for:
subq = session.query(X.id).join(Y).join(Z).filter(some condition)
If the query is complex, you might have to iterate on this several
times
Step 2. Add subquery()
subq = subq.subquery()
Step 3. use the subquery in final query (using a NOT IN example here)
qry = session.query(X).filter(~X.id.in_(subq))
Maybe use the subquery in a join
subq = session.query(X.id, Y.data, Z.colum).join(Y).join(Z).filter(some
condition)
qry = session.query(X.data, subq.c.colum).join((subq, subq.c.id==X.id))
I'm still learning this stuff, all I can suggest is practice a lot of
examples.
Given that, your example might look something like this, assuming you have
mappers describing all the relationships for the joins.
subq = session.query(SLE.id).join(Votes).\
join(States).join(Users).\
filter(Users.name=='admin').subquery()
qry = session.query(SLE).filter(not_(SLE.id.in_(subq)))
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
