On Nov 21, 2009, at 12:30 PM, jcm wrote:
> 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'm not aware of any mailing list message or currently open ticket regarding
any problems at all with parenthesization in current versions of SQLAlchemy.
> 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.
Here is entirely idiomatic SQLAlchemy code which approximates the types of
constructs your code snippets below are using - it places both the Query object
into a NOT IN, as well as the select() construct generated by Query.subquery()
within NOT IN, and generates valid SQL, including against the MySQL dialect.
So I cannot reproduce your issue, going back to version 0.5.3. I would
recommend that after confirming the issue with the latest 0.5 version of
SQLAlchemy, provide a full suite of all involved tables and mappers (but no
more), and the exact steps to reproduce (no reflected tables please),
preferably as a single Python script that runs with no dependencies. If
you've been observing the mailing list closely you'd know that genuine bugs of
significant severity are fixed within hours, so this is the best way for your
problem to be solved.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql://scott:ti...@localhost/test', echo=True)
Base = declarative_base(engine)
class Foo(Base):
__tablename__ ='foo'
id = Column('id', Integer, primary_key=True)
data = Column('data', Integer)
bars = relation("Bar")
class Bar(Base):
__tablename__ ='bar'
id = Column('id', Integer, primary_key=True)
foo_id = Column('foo_id', Integer, ForeignKey('foo.id'))
data = Column('data', Integer)
Base.metadata.create_all()
sess = create_session()
query1 = sess.query(Foo.id).join(Foo.bars).filter(Bar.data==5)
query2 = query1.subquery()
# use NOT IN (query)
query3
=sess.query(Foo).join(Foo.bars).filter(Bar.data==6).filter(~Foo.id.in_(query1))
# use NOT IN (query.subquery())
query4
=sess.query(Foo).join(Foo.bars).filter(Bar.data==6).filter(~Foo.id.in_(query2))
print query3
print list(query3)
print query4
print list(query4)
>
> 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=.
>
>
--
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=.
