On Jun 16, 2012, at 11:41 AM, Michael Bayer wrote:
>
> It's not a basic question at all as this is a rare edge case, and it's not a
> "foreign key" by definition. You need to relate the two tables based on a
> SQL function, in this case a concatenation. In some cases this can be
> tricky, and there's improvements in 0.8 to address that, though in this case
> it seems to work without too much difficulty:
ha ha, except that example was running in 0.8 :). Prior to 0.8 you need to
use an undocumented attribute _local_remote_pairs. Undocumented because, it
was never the best way to do this and in 0.8 it isn't needed anymore. But for
now:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class A(Base):
__tablename__ = "a"
id1 = Column(String, primary_key=True)
id2 = Column(String, primary_key=True)
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(String)
A.bs = relationship("B",
primaryjoin="B.a_id == A.id1 + A.id2",
foreign_keys="B.a_id",
_local_remote_pairs=[(A.__table__.c.id1, B.__table__.c.a_id),
(A.__table__.c.id2, B.__table__.c.a_id)],
viewonly=True)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(id1="x", id2="y", bs=[
B(a_id="xy"),
B(a_id="xy")
]),
A(id1="q", id2="p", bs=[
B(a_id="qp")
])
])
s.commit()
print s.query(A).first().bs
for a in s.query(A).options(joinedload(A.bs)):
print a.bs
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
