thanks for the help. it probably makes sense to alias the on-clause to
the query then, i just wasn't thinking in that way for some reason.
> So here you want to first call reset_joinpoint() before calling join() again.
tried this and got the same result (doesn't join/outerjoin reset the
join point by default anyway):
sess = Session()
AliasedA = orm.aliased(A)
q = sess.query(A)
q = q.join((AliasedA,
A.some_a)).options(orm.contains_eager(A.some_a,alias=AliasedA))
q = q.reset_joinpoint() # added
q = q.join((B, A.some_b)).options(orm.contains_eager(A.some_b))
print q
> use the separate orm.join() construct in conjunction with select_from()
started converting over to this but on a whim instead aliased all
joined targets *and* used that alias when specifying relations (as
well as in the relation chain passed to contains_eager).
it looks like that provides query.join/outerjoin enough information to
choose the desired lhs in the join clause.
updated example:
=================
from sqlalchemy import create_engine, Table, MetaData, orm, Column,
Integer, ForeignKey, String
from sqlalchemy.interfaces import PoolListener
class Listener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute("PRAGMA foreign_keys=ON")
engine = create_engine(
"sqlite:///:memory:",
listeners=[Listener()],
echo=True)
Session = orm.sessionmaker(bind=engine)
metadata = MetaData()
c_table = Table(
"c", metadata,
Column("id", Integer, primary_key=True),
Column("c_data", String)
)
a_table = Table(
"a", metadata,
Column("id", Integer, primary_key=True),
Column("a_data", String),
Column("c_id", ForeignKey(c_table.c.id))
)
assoc_table = Table(
"assoc", metadata,
Column("left_id", ForeignKey(a_table.c.id)),
Column("right_id", ForeignKey(a_table.c.id))
)
b_table = Table(
"b", metadata,
Column("id", Integer, primary_key=True),
Column("a_id", ForeignKey(a_table.c.id)),
Column("b_data", String)
)
class C(object):
@staticmethod
def apply_relations(q, spec, parent, chain):
for name, sub_spec in spec.iteritems():
raise ValueError("Invalid relation '%s'" % name)
return q
orm.mapper(C, c_table)
class B(object):
@staticmethod
def apply_relations(q, spec, parent, chain):
for name, sub_spec in spec.iteritems():
raise ValueError("Invalid relation '%s'" % name)
return q
orm.mapper(B, b_table)
class A(object):
@staticmethod
def apply_relations(q, spec, parent, chain):
for name, sub_spec in spec.iteritems():
if name == "some_a":
AliasA = orm.aliased(A)
chain.append(parent.some_a)
q = q.outerjoin((AliasA,
parent.some_a)).options(orm.contains_eager(*chain, alias=AliasA))
q = A.apply_relations(q, sub_spec, AliasA, chain)
del chain[-1]
elif name == "some_b":
AliasB = orm.aliased(B)
chain.append(parent.some_b)
q = q.outerjoin((AliasB,
parent.some_b)).options(orm.contains_eager(*chain, alias=AliasB))
q = B.apply_relations(q, sub_spec, AliasB, chain)
del chain[-1]
elif name == "c":
AliasC = orm.aliased(C)
chain.append(parent.c)
q = q.outerjoin((AliasC,
parent.c)).options(orm.contains_eager(*chain, alias=AliasC))
q = C.apply_relations(q, sub_spec, AliasC, chain)
del chain[-1]
else:
raise ValueError("Invalid relation '%s'" % name)
return q
orm.mapper(
A, a_table,
properties={
"some_a": orm.relation(
A,
primaryjoin=a_table.c.id ==
assoc_table.c.left_id,
secondary=assoc_table,
secondaryjoin=assoc_table.c.right_id ==
a_table.c.id),
"some_b": orm.relation(
B,
primaryjoin=b_table.c.a_id == a_table.c.id),
"c": orm.relation(C)
}
)
relation_spec = {
"some_a": {
"c": {
},
"some_b": {
},
"some_a": {
"some_a": {
"c": {
}
},
"some_b": {
},
},
},
"some_b": {
},
"c": {
}
}
sess = Session()
q = sess.query(A)
q = A.apply_relations(q, relation_spec, A, [])
#q = sess.query(A)
#AliasedA1 = orm.aliased(A)
#q = q.join((AliasedA1,
A.some_a)).options(orm.contains_eager(A.some_a, alias=AliasedA1))
#AliasedA1C1 = orm.aliased(C)
#q = q.join((AliasedA1C1,
AliasedA1.c)).options(orm.contains_eager(A.some_a, AliasedA1.c,
alias=AliasedA1C1))
#AliasedA1B1 = orm.aliased(B)
#q = q.join((AliasedA1B1,
AliasedA1.some_b)).options(orm.contains_eager(A.some_a,
AliasedA1.some_b, alias=AliasedA1B1))
#AliasedA1A1 = orm.aliased(A)
#q = q.join((AliasedA1A1,
AliasedA1.some_a)).options(orm.contains_eager(A.some_a,
AliasedA1.some_a, alias=AliasedA1A1))
#AliasedB1 = orm.aliased(B)
#q = q.join((AliasedB1,
A.some_b)).options(orm.contains_eager(A.some_b, alias=AliasedB1))
print q
=================
On Sep 20, 7:56 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 19, 2010, at 11:59 PM, me wrote:
>
>
>
> > when i apply multiple joins to orm query, one of them self-referring,
> > using (target, property) form sqla generates the "wrong" join criteria
> > by selecting aliased table for the left side of the join.
>
> > sess = Session()
> > AliasedA = orm.aliased(A)
> > q = sess.query(A)
> > q = q.join((AliasedA, A.some_a)).options(orm.contains_eager(A.some_a,
> > alias=AliasedA))
> > q = q.join((B, A.some_b)).options(orm.contains_eager(A.some_b))
> > print q
>
> > -->
>
> > SELECT
> > b.id AS b_id, b.a_id AS b_a_id, b.b_data AS b_b_data,
> > a_1.id AS a_1_id, a_1.a_data AS a_1_a_data,
> > a.id AS a_id, a.a_data AS a_a_data
> > FROM a
> > JOIN assoc AS assoc_1 ON a.id = assoc_1.left_id
> > JOIN a AS a_1 ON assoc_1.right_id = a_1.id
> > JOIN b ON b.a_id = a_1.id
> > ========================
>
> > here i expected "JOIN b ON b.a_id = a.id" not "JOIN b ON b.a_id =
> > a_1.id".
>
> This one is fun. join() always joins from the most recent FROM that it can.
> So here you want to first call reset_joinpoint() before calling join()
> again. The other "traditional" way is to use the separate orm.join()
> construct in conjunction with select_from(), though we are continuing to
> refine join() so that wont be needed (I eventually would like it to allow
> (left, right, onclause)...but we need to get everyone on a modernized calling
> form first).
>
> > perhaps weight relations so that those that introduce alias are
> > applied in right order?
>
> interesting....but this is really a 50/50 guess. Its just as likely someone
> really meant to join from AliasedA to B.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
