[sqlalchemy] Re: unions and order_by
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. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: unions and order_by
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)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: unions and order_by
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: unions and order_by
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:
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
-~--~~~~--~~--~--~---
