Since this is the association object pattern, I’ll describe that first. The pattern there is a little complicated, but if you can go with a straight many-to-many, it is then much easier.
The relationship as specified here is from A to A_to_B. If I have an “A” row
loaded into some_a, and assume the value of some_a.id is “1”. Then I request
the .a_to_b collection:
a_to_bs = some_a.a_to_b
the SQL emitted will look like:
SELECT a_to_b.* FROM a_to_b WHERE a_to_b.a_id = 1
because we want to load all the A_to_B objects matching our some_a.id of “1”.
So if you want to ORDER BY “b.order”, we need to pull “b” into that. It would
have to be:
SELECT a_to_b.* FROM a_to_b JOIN b ON a_to_b.b_id = b.id WHERE a_to_b.a_id
= 1 ORDER BY b.order
To do this, in the past we’ve had people just add “b” to their primaryjoin
condition:
relationship(“A_to_B”, primaryjoin="and_(A_to_B.a_id == A.id,
A_to_B.b_id == B.id)”, order_by=“B.id”)
That above will work for a lazy load, but not for a joined eager load, if you
need to do that - the above will load from A_to_B and B but not actually render
a JOIN. So the other way is to map to a SELECT or JOIN with a non-primary
mapper, documentation on that is here:
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
you’d here use:
from sqlalchemy import select
from sqlalchemy.orm import mapper
stmt = select([A_to_B, B.order]).select_from(join(A_to_B, B,
B.id==A_to_B.b_id)).alias()
a_to_b_b = mapper(A_to_B, stmt, non_primary=True)
a_to_b = relationship(a_to_b_b, order_by=stmt.c.order)
you can map to a JOIN instead of a SELECT which produces a more efficient
query, but then you need to be more careful about the attribute names being
mapped as the mapper will complain about naming conflicts (the doc includes an
example of that).
So that is all association object. Because “B” isn’t involved in loading from
A to A_to_B, you need to artificially bring “B” in. If OTOH you can use a
relationship that specifies “secondary” and goes straight from A to B, then you
have “B” right there in the SQL that’s emitted:
relationship(“B”, secondary=A_to_B.__table__, order_by=“B.order”)
You can use both patterns at the same time. The docs warn you to be careful,
since the two collections aren’t coordinated together and can be out of sync,
but it is doable.
On Feb 27, 2014, at 3:30 PM, Seth P <spadow...@gmail.com> wrote:
> Just noticed that I had a typo, where I wrote "order_by=b.ordinal" rather
> than "order_by=b.order". But changing it to "order_by=b.order" still gives:
> AttributeError: 'RelationshipProperty' object has no attribute 'order'
>
>
> --
> 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/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
