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