On Mar 26, 2014, at 1:04 PM, Staszek <stf.list.ot...@eisenbits.com> wrote:
> Hi
>
> I have a problem with joined relationships, which I will try to describe
> using an example:
>
>
> 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)
It's not totally clear which of the common use cases you have here, pick one:
1. I want to emit a JOIN from User to Car and order by Car.model - don't use
eager loading, use query.join():
http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join
2. I want to load User objects and eagerly load the User.cars collection, and
the Car collection should be ordered by "model" in all cases: use
relationship()->order_by:
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html?highlight=relationship#sqlalchemy.orm.relationship
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html?highlight=relationship#sqlalchemy.orm.relationship.params.order_by
3. I want to load User objects and eagerly load the Car objects, but only in
this *one particular instance* do i want the ordering, otherwise I want (some
other? no?) ordering. Well for that we have the join() + contains_eager().
3a: no, I actually have use case #3, I don't want it ordered by "model" most
of the time, only occasionally, and I don't like typing out two functions, I
only want to type one function. OK, well I can see how maybe someday the
XYZload() functions could allow an "order_by" feature, that might be nice (also
I will gladly implement this feature ASAP if you'd like to sponsor it). For
now you need to just use regular Python:
def joinedload_plus_order_by(query, relationship, ordering):
return
query.join(relationship).options(contains_eager(relationship)).order_by(ordering)
example:
q = sess.query(User)
q = joinedload_plus_order_by(q, User.cars, Car.model)
I'd have to disagree with your assertions that "relationship is useless" as we
are totally using User.cars here to formulate the JOIN, as well as "order by
blindly copies the table name", that's not at all how it works. For some
insight on the aliasing applied by eager loading please read
http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading
.
--
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.
