foreign_keys logic with overlapping composite foreign keys

[Mike Bayer]

from sqlalchemy import *

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import *


Base = declarative_base()

class User(Base):
    __tablename__ = "user"
    uid = Column(String(600), primary_key=True)
    oid = Column(String(100), primary_key=True)
    name = Column(String(600), nullable=False)
    lastname = Column(String(600), nullable=False)

class Bet(Base):
    __tablename__ = "bet"
    __table_args__ = (
       ForeignKeyConstraint(("uid", "oid"), ("user.uid", "user.oid")),
       ForeignKeyConstraint(("loader_uid", "oid"), ("user.uid", "user.oid")),
    )
    id = Column(Integer, autoincrement=True, primary_key=True)
    uid = Column(String(600), nullable=False)
    loader_uid = Column(String(600), nullable=False)
    oid = Column(String(100), nullable=False)

    runner = relationship("User",
                     # passes
                     #primaryjoin="and_(foreign(Bet.uid)==User.uid, foreign(Bet.oid)==User.oid)",

                     # fails
                     foreign_keys=[loader_uid, oid]
                     )
    loader = relationship("User",
                     # passes
                     #primaryjoin="and_(foreign(Bet.loader_uid)==User.uid, foreign(Bet.oid)==User.oid)",

                      # fails
                     foreign_keys=[uid, oid]
                        )

configure_mappers()

print Bet.runner.__clause_element__()
print Bet.loader.__clause_element__()

[Mike Bayer]

diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index d59b45f..7a220f9 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -24,6 +24,7 @@ from .. import exc
 from operator import attrgetter
 from . import operators
 import operator
+import collections
 from .annotation import Annotated
 import itertools

@@ -682,8 +683,7 @@ class Join(FromClause):
         providing a "natural join".

         """
-        crit = []
-        constraints = set()
+        constraints = collections.defaultdict(list)

         for left in (a_subset, a):
             if left is None:
@@ -703,8 +703,7 @@ class Join(FromClause):
                         continue

                 if col is not None:
-                    crit.append(col == fk.parent)
-                    constraints.add(fk.constraint)
+                    constraints[fk.constraint].append((col, fk.parent))
             if left is not b:
                 for fk in sorted(
                             left.foreign_keys,
@@ -723,12 +722,36 @@ class Join(FromClause):
                             continue

                     if col is not None:
-                        crit.append(col == fk.parent)
-                        constraints.add(fk.constraint)
-            if crit:
+                        constraints[fk.constraint].append((col, fk.parent))
+            if constraints:
                 break

-        if len(crit) == 0:
+        if len(constraints) > 1:
+            # more than one constraint matched.  narrow down the list
+            # to include just those FKCs that match exactly to
+            # "consider_as_foreign_keys".
+            if consider_as_foreign_keys:
+                for const in list(constraints):
+                    if set(f.parent for f in const.elements) != set(consider_as_foreign_keys):
+                        del constraints[const]
+
+            # if still multiple constraints, but
+            # they all refer to the exact same end result, use it.
+            if len(constraints) > 1:
+                dedupe = set(tuple(crit) for crit in constraints.values())
+                if len(dedupe) == 1:
+                    key = constraints.keys()[0]
+                    constraints = {key, constraints[key]}
+
+            if len(constraints) != 1:
+                raise exc.AmbiguousForeignKeysError(
+                    "Can't determine join between '%s' and '%s'; "
+                    "tables have more than one foreign key "
+                    "constraint relationship between them. "
+                    "Please specify the 'onclause' of this "
+                    "join explicitly." % (a.description, b.description))
+
+        if len(constraints) == 0:
             if isinstance(b, FromGrouping):
                 hint = " Perhaps you meant to convert the right side to a "\
                                     "subquery using alias()?"
@@ -737,14 +760,9 @@ class Join(FromClause):
             raise exc.NoForeignKeysError(
                 "Can't find any foreign key relationships "
                 "between '%s' and '%s'.%s" % (a.description, b.description, hint))
-        elif len(constraints) > 1:
-            raise exc.AmbiguousForeignKeysError(
-                "Can't determine join between '%s' and '%s'; "
-                "tables have more than one foreign key "
-                "constraint relationship between them. "
-                "Please specify the 'onclause' of this "
-                "join explicitly." % (a.description, b.description))
-        elif len(crit) == 1:
+
+        crit = [(x == y) for x, y in constraints.values()[0]]
+        if len(crit) == 1:
             return (crit[0])
         else:
             return and_(*crit)

[Mike Bayer]

• Improved the check for "how to join from A to B" such that when a table has multiple, composite foreign keys targeting a parent table, the :paramref:.relationship.foreign_keys argument will be properly interpreted in order to resolve the ambiguity; previously this condition would raise that there were multiple FK paths when in fact the foreign_keys argument should be establishing which one is expected. fixes #2965

→ <<cset 0611baa88919>>