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 <[email protected] <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 <[email protected]> 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 [email protected].
>> To post to this group, send email to [email protected].
>> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
