you shouldnt be using order_by on your mapper(). thats a really old option in any case. if you need it to be there, say query.order_by(None).statement to cancel the order_by in each separate part of the union. however it would be even easier if you just said query.union(q1, q2) here (still might need the order_by(None) though).
On Jan 24, 2009, at 3:56 PM, Brett wrote: > > The script below is giving me the following error: > > sqlalchemy.exc.OperationalError: (OperationalError) ORDER BY clause > should come after UNION not before u'SELECT anything.id, anything.any, > anything.something_id \nFROM anything JOIN something ON something.id = > anything.something_id ORDER BY any UNION SELECT anything.id, > anything.any, anything.something_id \nFROM anything JOIN something ON > something.id = anything.something_id ORDER BY any' [] > > This works with 0.5.0rc4 but started showing up with 0.5.0. > > The SQL generated with 0.5.0rc4: > > SELECT anything.id, anything.any, anything.something_id > FROM anything JOIN something ON something.id = anything.something_id > UNION SELECT anything.id, anything.any, anything.something_id > FROM anything JOIN something ON something.id = anything.something_id > > The SQL generated with 0.5.2: > > SELECT anything.id, anything.any, anything.something_id > FROM anything JOIN something ON something.id = anything.something_id > ORDER BY any UNION SELECT anything.id, anything.any, > anything.something_id > FROM anything JOIN something ON something.id = anything.something_id > ORDER BY any > > > Is this a bug or do I have something setup wrong? > > > ------------------------------------------------- > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class Something(Base): > __tablename__ = 'something' > __mapper_args__ = {'order_by': 'some'} > id = Column(Integer, primary_key=True) > some = Column(String) > > class Anything(Base): > __tablename__ = 'anything' > __mapper_args__ = {'order_by': 'any'} > id = Column(Integer, primary_key=True) > any = Column(String) > something_id = Column(Integer, ForeignKey('something.id')) > somethings = relation(Something) > > uri = 'sqlite:///:memory:' > engine = create_engine(uri) > engine.connect() > metadata = Base.metadata > metadata.bind = engine > metadata.create_all() > > Session = sessionmaker(bind=engine) > session = Session() > > q1 = session.query(Anything).join('somethings') > q2 = session.query(Anything).join('somethings') > > u = union(q1.statement, q2.statement) > print list(session.query(Anything).from_statement(u)) > > ------------------------------------------------- > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---