join to single-inh subclass without using a property-based join fails to apply single inh criterion
Issue #3222
resolved
from sqlalchemy import Integer, ForeignKey, Column, String
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
type = Column(String)
cid = Column(ForeignKey('c.id'))
c = relationship("C", backref="a")
__mapper_args__ = {'polymorphic_on': type}
class B(A):
__mapper_args__ = {'polymorphic_identity': 'b'}
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
s = Session()
print s.query(C, B).filter(C.id == B.cid)
print s.query(C, B).join(B, C.id == B.cid)
print s.query(C, B).join(B, C.a)
first query is OK:
SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid
FROM c, a
WHERE c.id = a.cid AND a.type IN (:type_1)
second query lacks the discriminator:
SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid
FROM c JOIN a ON c.id = a.cid
third one is OK:
SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid
FROM c JOIN a ON c.id = a.cid AND a.type IN (:type_1)
the issue is the check inside of _join_entities assumes the ON clause was rendered orm.join() which also adds the criteria in. So this patch resolves:
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7b2ea79..0c3bc26 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1979,7 +1979,7 @@ class Query(object):
info.selectable, \
getattr(info, 'is_aliased_class', False)
- if right_mapper:
+ if right_mapper and prop is not None:
self._join_entities += (info, )
if right_mapper and prop and \
Comments (6)
-
reporter -
reporter OK let's do this instead. Since we are OK adding WHERE to queries, we can augment the ON clause here, but this is now a 1.0 thing:
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 7b2ea79..8de4946 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -1980,6 +1980,11 @@ class Query(object): getattr(info, 'is_aliased_class', False) if right_mapper: + if not prop and right_mapper.single: + single_crit = right_mapper._single_table_criterion + if right_is_aliased: + single_crit = info._adapter.traverse(single_crit) + onclause = sql.and_(onclause, single_crit) self._join_entities += (info, ) if right_mapper and prop and \
-
reporter - changed milestone to 1.0
- edited description
-
reporter Okey doke, more complicated, needs to not screw up when we do query(A).join(B), no ON clause at all. Here's that:
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py index 734f9d5..916e032 100644 --- a/lib/sqlalchemy/orm/util.py +++ b/lib/sqlalchemy/orm/util.py @@ -775,6 +775,7 @@ class _ORMJoin(expression.Join): else: prop = None + single_crit = None if prop: if sql_util.clause_is_present( on_selectable, left_info.selectable): @@ -801,9 +802,19 @@ class _ORMJoin(expression.Join): else: onclause = pj self._target_adapter = target_adapter + elif right_info and right_info.mapper.single: + # if single inheritance target and we are using a manual + # ON clause, augment it the same way we'd augment the + # WHERE. + single_crit = right_info.mapper._single_table_criterion + if right_info.is_aliased_class: + single_crit = right_info._adapter.traverse(single_crit) expression.Join.__init__(self, left, right, onclause, isouter) + if single_crit is not None: + self.onclause = self.onclause & single_crit + def join(self, right, onclause=None, isouter=False, join_to_left=None): return _ORMJoin(self, right, onclause, isouter)
-
reporter - changed status to resolved
- The ON clause rendered when using :meth:
.Query.join
, :meth:.Query.outerjoin
, or the standalone :func:.orm.join
/ :func:.orm.outerjoin
functions to a single-inheritance subclass will now include the "single table criteria" in the ON clause even if the ON clause is otherwise hand-rolled; it is now added to the criteria using AND, the same way as if joining to a single-table target using relationship or similar. fixes#3222
→ <<cset dd6389f17173>>
-
reporter - add a migration note for ref
#3222
→ <<cset be8d361be286>>
- add a migration note for ref
- Log in to comment
unfortunately this patch fails to do the right thing with an outer join, so we can't put this in as is and we might want to WONTFIX it:
result:
so unfortunately, if the user is hand-rolling the ON criterion, they have to also include the "single inheritance crit" - it has to be in the ON clause.