Oh wow, haha, this is pretty awesome. Never thought I'd use mapper by itself at all! Thanks a lot! I guess the "ancient technique" is still useful for something, eh
On Wednesday, June 12, 2013 11:36:21 AM UTC-5, Michael Bayer wrote: > > Well for proxies you'd need to roll part of it manually, like a custom > collection that filters, that kind of thing. > > But no matter. I'll apologize up front you don't get the hear the raucous > cackle I made when I got this to work. Because it required heading down > the dusty stairs to unlock the ancient texts, calling upon a technique that > in the early days I thought would be commonplace, but now is mostly unheard > of. The "non-primary mapper" means you're going to make a second map of a > class - mostly obsolete for querying because you can pass any kind of > selectable into a Query anyway using aliased(). But it is still useful > when you need to get a very unusual thing into relationship(). in 0.9, > you can join to this mapper without the nested SELECT as long as you're not > on sqlite, but this all works great in 0.8 too. > > from sqlalchemy import * > from sqlalchemy.ext.associationproxy import association_proxy > from sqlalchemy.ext.declarative.api import declarative_base > from sqlalchemy.orm import * > > Base = declarative_base() > > class A(Base): > __tablename__ = 'table_a' > id = Column(Integer, primary_key=True) > child_id = Column(Integer, ForeignKey('table_a.id')) > children = relationship('A', backref=backref('parent', > remote_side=[id])) > > class B(Base): > __tablename__ = 'table_b' > id = Column(Integer, primary_key=True) > a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True) > > a_re = relationship('A', backref='b_re') > > b_table = B.__table__ > a_table = A.__table__ > > # this is easier to map to an alias like this: > #child_bs = select([b_table, a_table.c.child_id], > # > use_labels=True).select_from(b_table.join(a_table)).alias() > > # but mapping straight to the JOIN we get simpler queries > # (for lazyloads in 0.8, joins/joinedloads too in 0.9) > child_bs = b_table.join(a_table) > > cbm = mapper(B, child_bs, properties=dict( > # make sure attribute names line up > # with our original names... > id=child_bs.c.table_b_id, > a_id=child_bs.c.table_a_id, > _b_a_id=child_bs.c.table_b_a_id, > ), > non_primary=True) > > B.children = relationship(cbm, > primaryjoin=and_( > B.id == foreign(remote(child_bs.c.table_b_id)), > B.a_id == > foreign(remote(child_bs.c.table_a_child_id)) > > # or can get to the cols using mapped names > # B.id == foreign(remote(cbm.c.id)), > # B.a_id == foreign(remote(cbm.c.child_id)) > ), > viewonly=True, > collection_class=set) > > e = create_engine("sqlite://", echo=True) > > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > > s = Session(e) > > a1 = A() > a2 = A(parent=a1) > a3 = A(parent=a1) > a4 = A() > > b1 = B(id=1, a_re=a1) > b2 = B(id=1, a_re=a2) > b3 = B(id=1, a_re=a3) > b4 = B(id=1, a_re=a4) > > b5 = B(id=2, a_re=a1) > b6 = B(id=2, a_re=a2) > b7 = B(id=2, a_re=a3) > b8 = B(id=2, a_re=a4) > > s.add_all([ > a1, a2, a3, a4 > ]) > > s.commit() > > assert b1.children == set([b2, b3]) > assert b5.children == set([b6, b7]) > s.expire_all() > > # oh yes > for beta in s.query(B).options(joinedload("children")): > for b in beta.children: > assert b.id == beta.id > assert b.a_re in beta.a_re.children > > > > On Jun 11, 2013, at 6:34 PM, Greg Yang <sorcer...@gmail.com <javascript:>> > wrote: > > How would you use proxies? I can get B.a_re.children.b_re, but this > includes all Bs that have different B.id than I want along with the ones I > do want. I could just use a @property that issues SQL on every call, but > I'm trying to see if there are more efficient ways of doing this. > > On Tuesday, June 11, 2013 4:18:20 PM UTC-5, Michael Bayer wrote: >> >> >> getting it to work with "secondary" or only "primaryjoin" as it sometimes >> works out is fairly complex and might not be possible. If "secondary", >> you might need to make "secondary" an aliased SELECT statement, or in 0.9 >> maybe it can be a a JOIN, that represents all the intermediary rows. Might >> work, might not, would have to spend a few hours with it. >> >> Is there a reason you can't just route to the related >> B.a.children.bs<http://b.a.children.bs/>using proxies? Or a @property >> based loader? >> >> >> >> >> On Jun 11, 2013, at 4:45 PM, Greg Yang <sorcer...@gmail.com> wrote: >> >> Consider these 2 mapped classes >> >> from sqlalchemy.engine import create_engine >> from sqlalchemy.ext.associationproxy import association_proxy >> from sqlalchemy.ext.declarative.api import declarative_base >> from sqlalchemy.orm import relationship >> from sqlalchemy.orm.session import sessionmaker >> from sqlalchemy.orm.util import aliased >> from sqlalchemy.schema import Column, ForeignKey >> from sqlalchemy.sql.expression import and_ >> from sqlalchemy.types import Integer, String >> >> Base = declarative_base() >> >> class A(Base): >> __tablename__ = 'table_a' >> id = Column(Integer, primary_key=True) >> child_id = Column(Integer, ForeignKey('table_a.id')) >> children = relationship('A', backref = 'parent', remote_side=[id]) >> class B(Base): >> __tablename__ = 'table_b' >> id = Column(Integer, primary_key=True) >> a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True) >> a_re = relationship('A', backref='b_re') >> >> What I want to do is have a self-referential relationship in B that >> routes through A's children relationship while keeping B.id the same. More >> explicitly I want some relationship B.children such that for any instance >> beta of B >> >> for b in beta.children: >> assert b.id == beta.id >> assert b.a_re in beta.a_re.children >> >> Now, if the condition b.id == beta.id is ignored, then it's just a plain >> association table many-to-many relationship, something like >> >> B.children = relationship('B', secondary=A.__table__, >> primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True) >> >> But with the b.id == beta.id condition I need to refer to table_b twice >> in the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do >> that in relationship. >> >> I've tried this >> >> BB = aliased(B) >> B.children = relationship('BB', secondary=A.__table__, >> primaryjoin=B.a_id==A.id, >> secondaryjoin='''and_(A.id==BB.a_id, >> B.id==BB.id)''', >> viewonly=True) >> >> but it seems like BB is not recognized by the ORM in mapping. >> >> How do I do this? >> >> -- >> 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. >> To post to this group, send email to sqlal...@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. >> 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+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.