On Nov 11, 10:22 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> > 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
>
> since you got some help here, I'll also note that I was surprised I didn't
> have "NOT IN" in the docs so I added that to the ORM tutorial.
Thanks. But there's still a problem preventing me from actually using
this. And it's the inability for sqlalchemy to insert correct
parentheses in the subquery it generates (which has come up here
before). I've wasted all night coming to the realization that the
problem is SQLA and not my code - it should add '(' ')' around the
select in the subquery. I tried having it return a statement and then
calling self_group, and recreating a new query based on that. But
there's just no getting around the fact that it won't generate the SQL
I want it to. Hopefully, there's some hacky way that we can get it to
DTRT at least for the moment.
For context, what I'm doing is writing a Linux kernel symbol tracking
database for a commercial Linux kernel. The Linux kernel contains
approximately 10,000 exported symbols (think software functions) that
third parties can consume. But not all of these symbols should be used
by third parties, and so it is necessary to limit the symbols to a
set. That set is determined through a collaborative voting process in
which people can vote for given proposed symbol entries in the
database. So we have Symbol (the symbol itself), SymbolList (a list
containing this symbol), SymbolListEntry (an entry on that list), and
other types (there are 54 in total). A Vote is requested from the user
against each SymbolListEntry that they have not voted on. In order to
only present entries that haven't been voted on before, I do things
like (simplified):
unvoted_proposed_add_symbollistentries = DBSession.query
(SymbolListEntry).from_statement("SELECT symbollistentries.* FROM
symbollistentries JOIN states ON
(states.state_id=symbollistentries.state_id) WHERE
symbollistentries.proposed_add=1 AND states.deleted=FALSE and
states.active=FALSE AND 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_id=:user_id AND
votes.revision=symbollistentries.revision)").params
(user_id=user.user_id).all()
Which is ugly. I've managed to kill the from_statements everywhere
else in the app now, but not here. I've since added a few more checks
of state in the above query (nothing is ever deleted, only recorded as
such in 'states', hence the weird stuff). Anyway, here's some example
code close to what I want to do, but MySQL balks on the code that SQLA
emits from this:
symbolListEntryState = aliased(State)
voteState = aliased(State)
previous_votes = DBSession.query
(SymbolListEntry.symbollistentry_id).join
((symbolListEntryState,SymbolListEntry.state)).filter(and_
(symbolListEntryState.deleted==False,symbolListEntryState.active==False)).join
((Vote,SymbolListEntry.votes)).filter
(Vote.revision==SymbolListEntry.revision).join
((voteState,Vote.state)).filter(and_
(voteState.deleted==False,voteState.active==True)).join
((User,voteState.owner)).filter(User.user_id==user.user_id).subquery()
symbollistentries = DBSession.query(SymbolListEntry).join
((symbolListEntryState,SymbolListEntry.state)).filter(and_
(symbolListEntryState.deleted==False,symbolListEntryState.active==False)).filter
(SymbolListEntry.proposed_add==True).filter
(~SymbolListEntry.symbollistentry_id.in_(previous_votes)).all()
return symbollistentries
It generates this:
ProgrammingError: (ProgrammingError) (1064, "You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'SELECT
symbollistentries.symbollistentry_id \nFROM symbollistentries INNER
JOIN s' at line 5") u'SELECT symbollistentries.symbollistentry_id AS
symbollistentries_symbollistentry_id, symbollistentries.symbol_id AS
symbollistentries_symbol_id, symbollistentries.symbollist_id AS
symbollistentries_symbollist_id, symbollistentries.state_id AS
symbollistentries_state_id, symbollistentries.proposed_add AS
symbollistentries_proposed_add, symbollistentries.proposed_remove AS
symbollistentries_proposed_remove, symbollistentries.revision AS
symbollistentries_revision, symbollistentries.went_live AS
symbollistentries_went_live \nFROM (SELECT
symbollistentries.symbollistentry_id AS symbollistentry_id \nFROM
symbollistentries INNER JOIN states AS states_1 ON
symbollistentries.state_id = states_1.state_id INNER JOIN votes ON
votes.symbollistentry_id = symbollistentries.symbollistentry_id INNER
JOIN states AS states_2 ON votes.state_id = states_2.state_id INNER
JOIN users ON states_2.owner_id = users.user_id \nWHERE
states_1.deleted = %s AND states_1.active = %s AND votes.revision =
symbollistentries.revision AND states_2.deleted = %s AND
states_2.active = %s AND users.user_id = %s) AS anon_1,
symbollistentries INNER JOIN states AS states_1 ON
symbollistentries.state_id = states_1.state_id \nWHERE
states_1.deleted = %s AND states_1.active = %s AND
symbollistentries.proposed_add = %s AND
symbollistentries.symbollistentry_id NOT IN SELECT
symbollistentries.symbollistentry_id \nFROM symbollistentries INNER
JOIN states AS states_1 ON symbollistentries.state_id =
states_1.state_id INNER JOIN votes ON votes.symbollistentry_id =
symbollistentries.symbollistentry_id INNER JOIN states AS states_2 ON
votes.state_id = states_2.state_id INNER JOIN users ON
states_2.owner_id = users.user_id \nWHERE states_1.deleted = %s AND
states_1.active = %s AND votes.revision = symbollistentries.revision
AND states_2.deleted = %s AND states_2.active = %s AND users.user_id =
%s' [0, 0, 0, 1, 1L, 0, 0, 1, 0, 0, 0, 1, 1L]
Which is quite clearly incorrect, looking at the "NOT IN" generated.
I'm going to wind up with some raw SQL selects left in this app for
now because I've spent 12 hours trying to figure out a way to get SQLA
to do it, and that's probably more than enough for a kernel engineer
who doesn't usually write web apps, but if you happen to have a magic
answer, it would be appreciated. I enjoy some things about SQLAlchemy,
but sometimes (like right here), all I want to do is rip my hair
out :)
Jon.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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=.
