Hi
I have a problem with joined relationships, which I will try to describe
using an example:
class User(Base):
__tablename__ = 'users'
__table_args__ = ...
id = Column(BigInteger, autoincrement=True, primary_key=True)
...
carId = Column(BigInteger, ForeignKey('cars.id'))
...
car = relationship("Car", lazy='joined', innerjoin=True)
Querying for User now seems to generate SQL expressions like this:
SELECT ... FROM users INNER JOIN cars AS cars_1 WHERE ...
The problem is, "cars_1" alias is auto-generated and the relationship
function:
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#sqlalchemy.orm.relationship
does not seem to provide any means to specify another name!
This leads to problems, for example if you want to build such a joined
query and then apply order_by(Car.model) to it: order_by(Car.model)
turns into "ORDER BY cars.model" which is an error because cars table in
this query is used with an auto-generated alias "cars_1"! The proper
ORDER BY clause would be: "ORDER BY cars_1.model".
And so one either has to "guess" this "cars_1" alias (but it does not
seem to be documented) or resource to things like explicit join(),
options() and contains_eager():
dbSession.query(User).join(User.car).options(contains_eager(User.car))
This sucks, especially because you must rewrite this query everywhere:
if you want to select companies with users with cars and
order_by(Car.model), you have to use explicit join()s to cars and
contains_eager() stuff. This makes User.car relationship basically
unusable with order_by().
I can see 2 possible fixes to this problem:
1. specify an 'join-alias' argument in relationship(), to be used with
lazy='joined', so that you have control over this "cars_1" alias and
could specify another name
2. make order_by check what table alias to use (instead of just blindly
copying the table name).
What do you think?
Or maybe I am missing something?...
--
http://people.eisenbits.com/~stf/
http://www.eisenbits.com/
OpenPGP: 80FC 1824 2EA4 9223 A986 DB4E 934E FEA0 F492 A63B
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
