Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Michael Bayer]

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

Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Seth P]

Thank you. This was very helpful.

One non-trivial thing that stumped me for a while is that if B is derived 
from a B_base using joined-table inheritance, and the order variable is in 
the base table B_base, then it seems one must include B_base explicitly -- 
as highlighted below.

from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, 
join
from sqlalchemy.orm import create_session, relationship, backref, mapper
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

sqlite = 'sqlite:///test_a_to_b.db'
e = create_engine(sqlite, echo=True)
Base = declarative_base(bind=e)

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = association_proxy(a_to_bs, b, creator=lambda x: A_to_B(b=x))

class B_base(Base):
__tablename__ = 'b_base'
id = Column(Integer, primary_key=True)
order = Column(Integer)

class B(B_base):
__tablename__ = 'b'
id = Column(Integer, ForeignKey(str(B_base.__table__) + .id), 
primary_key=True)

class A_to_B(Base):
__tablename__ = 'a_to_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
nullable=False, index=True)
a = relationship(A, foreign_keys=[a_id],
 backref=backref(a_to_bs, 
primaryjoin=and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id == 
B_base.id*), order_by=B.order))
b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
nullable=False)
b = relationship(B, foreign_keys=[b_id], lazy=subquery)
def __init__(self, a=None, b=None):
self.a = a
self.b = b

if __name__ == '__main__':
# recreate database
Base.metadata.drop_all()
Base.metadata.create_all()
# populate
session = create_session(bind=e, autocommit=False)
a = A(bs=[B(order=10), B(order=2)])
session.add(a)
session.commit()
session.close_all()
# read
session = create_session(bind=e, autocommit=False)
a = session.query(A).one()
for b in a.bs:
print b.order
for a_to_b in a.a_to_bs:
print a_to_b.b.order
session.close_all()

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

Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Michael Bayer]

On Feb 27, 2014, at 9:23 PM, Seth P spadow...@gmail.com wrote:

 Thank you. This was very helpful.
 
 One non-trivial thing that stumped me for a while is that if B is derived 
 from a B_base using joined-table inheritance, and the order variable is in 
 the base table B_base, then it seems one must include B_base explicitly -- as 
 highlighted below.

OK well maybe you just include B_base in the primaryjoin and not “B” at all?   
you want to include as few tables as possible in these things to keep the query 
plans simpler.



 
 from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, 
 join
 from sqlalchemy.orm import create_session, relationship, backref, mapper
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.ext.associationproxy import association_proxy
 
 sqlite = 'sqlite:///test_a_to_b.db'
 e = create_engine(sqlite, echo=True)
 Base = declarative_base(bind=e)
 
 class A(Base):
 __tablename__ = 'a'
 id = Column(Integer, primary_key=True)
 bs = association_proxy(a_to_bs, b, creator=lambda x: A_to_B(b=x))
 
 class B_base(Base):
 __tablename__ = 'b_base'
 id = Column(Integer, primary_key=True)
 order = Column(Integer)
 
 class B(B_base):
 __tablename__ = 'b'
 id = Column(Integer, ForeignKey(str(B_base.__table__) + .id), 
 primary_key=True)
 
 class A_to_B(Base):
 __tablename__ = 'a_to_b'
 id = Column(Integer, primary_key=True)
 a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
 nullable=False, index=True)
 a = relationship(A, foreign_keys=[a_id],
  backref=backref(a_to_bs, primaryjoin=and_(A_to_B.a_id 
 == A.id, A_to_B.b_id == B.id, B.id == B_base.id), order_by=B.order))
 b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
 nullable=False)
 b = relationship(B, foreign_keys=[b_id], lazy=subquery)
 def __init__(self, a=None, b=None):
 self.a = a
 self.b = b
 
 if __name__ == '__main__':
 # recreate database
 Base.metadata.drop_all()
 Base.metadata.create_all()
 # populate
 session = create_session(bind=e, autocommit=False)
 a = A(bs=[B(order=10), B(order=2)])
 session.add(a)
 session.commit()
 session.close_all()
 # read
 session = create_session(bind=e, autocommit=False)
 a = session.query(A).one()
 for b in a.bs:
 print b.order
 for a_to_b in a.a_to_bs:
 print a_to_b.b.order
 session.close_all()
 
 
 -- 
 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

Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Seth P]

Good point, but unfortunately, unless I'm missing something, including only 
B_base and removing B from the join doesn't seem to work when A is also 
derived (using joined-table inheritance) from B_base (which is my actual 
situation, despite what the nomenclature here suggest).

On Thursday, February 27, 2014 9:26:31 PM UTC-5, Michael Bayer wrote:


 On Feb 27, 2014, at 9:23 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 Thank you. This was very helpful.

 One non-trivial thing that stumped me for a while is that if B is derived 
 from a B_base using joined-table inheritance, and the order variable is in 
 the base table B_base, then it seems one must include B_base explicitly -- 
 as highlighted below.


 OK well maybe you just include B_base in the primaryjoin and not “B” at 
 all?   you want to include as few tables as possible in these things to 
 keep the query plans simpler.




 from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, 
 join
 from sqlalchemy.orm import create_session, relationship, backref, mapper
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.ext.associationproxy import association_proxy

 sqlite = 'sqlite:///test_a_to_b.db'
 e = create_engine(sqlite, echo=True)
 Base = declarative_base(bind=e)

 class A(Base):
 __tablename__ = 'a'
 id = Column(Integer, primary_key=True)
 bs = association_proxy(a_to_bs, b, creator=lambda x: A_to_B(b=x))

 class B_base(Base):
 __tablename__ = 'b_base'
 id = Column(Integer, primary_key=True)
 order = Column(Integer)

 class B(B_base):
 __tablename__ = 'b'
 id = Column(Integer, ForeignKey(str(B_base.__table__) + .id), 
 primary_key=True)

 class A_to_B(Base):
 __tablename__ = 'a_to_b'
 id = Column(Integer, primary_key=True)
 a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
 nullable=False, index=True)
 a = relationship(A, foreign_keys=[a_id],
  backref=backref(a_to_bs, 
 primaryjoin=and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id == 
 B_base.id*), order_by=B.order))
 b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
 nullable=False)
 b = relationship(B, foreign_keys=[b_id], lazy=subquery)
 def __init__(self, a=None, b=None):
 self.a = a
 self.b = b

 if __name__ == '__main__':
 # recreate database
 Base.metadata.drop_all()
 Base.metadata.create_all()
 # populate
 session = create_session(bind=e, autocommit=False)
 a = A(bs=[B(order=10), B(order=2)])
 session.add(a)
 session.commit()
 session.close_all()
 # read
 session = create_session(bind=e, autocommit=False)
 a = session.query(A).one()
 for b in a.bs:
 print b.order
 for a_to_b in a.a_to_bs:
 print a_to_b.b.order
 session.close_all()


 -- 
 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+...@googlegroups.com javascript:.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.




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