unions and order_by

[Brett]

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))

-------------------------------------------------

[Michael Bayer]

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

[Michael Bayer]

youre going to want to set order_by like this too instead of the
string 'any'

class Anything(Base):
__tablename__ = 'anything'

id = Column(Integer, primary_key=True)
any = Column(String)
something_id = Column(Integer, ForeignKey('something.id'))
somethings = relation(Something)

__mapper_args__ = {'order_by': any}

On Jan 24, 2009, at 3:56 PM, Brett wrote:

>

[Brett]

The "Controlling Ordering" section of the docs mentions that using
order_by on mappers is the standard way for setting a default ordering
against a single mapped entity. This seems like a good feature. Is
there another way? Will this be deprecated in the future?

What's also really weird is that if I put my __mapper_args__ at the
beginning of the definition of Anything then I get the following
errors for q1.union(q2), query.union(q1, q2) and query.union_all(q1,
q2):

File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/
sqlalchemy/sql/visitors.py", line 247, in clone
cloned[element] = element._clone()
AttributeError: 'str' object has no attribute '_clone'

If I put __mapper_args__ at the end it passes through. Also if I use
the original:

u = union(q1.statement, q2.statement)
print list(session.query(Anything).from_statement(u))

... then it doesn't matter where the __mapper_args__ are.

And for posterity, this is where the order_by(None)'s should be:

q1 = session.query(Anything).join('somethings').order_by(None)
q2 = session.query(Anything).join('somethings').order_by(None)
q = session.query(Anything).order_by(None).union(q1, q2)

[Michael Bayer]

On Jan 25, 2009, at 8:41 PM, Brett wrote:

>
> The "Controlling Ordering" section of the docs mentions that using
> order_by on mappers is the standard way for setting a default ordering
> against a single mapped entity. This seems like a good feature. Is
> there another way? Will this be deprecated in the future?

it wont be deprecated.

>
>
> What's also really weird is that if I put my __mapper_args__ at the
> beginning of the definition of Anything then I get the following
> errors for q1.union(q2), query.union(q1, q2) and query.union_all(q1,
> q2):
>
> File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/
> sqlalchemy/sql/visitors.py", line 247, in clone
> cloned[element] = element._clone()
> AttributeError: 'str' object has no attribute '_clone'

its because youre using a string for the order by in the
__mapper_args__. use the Column object as the argument for order_by
instead.

>
>
> If I put __mapper_args__ at the end it passes through. Also if I use
> the original:
> u = union(q1.statement, q2.statement)
> print list(session.query(Anything).from_statement(u))

in this case, the Query receives your statement and doesn't try to
change it. in all other cases, the query selects "from" your
statement and its the processing to generate that SQL which blows up
on the string order_by as the mapper argument.