use foreign_keys to determine primaryjoin

very simple and the slightest bit not backwards compatible....very unfortunate I wasn't able to see this four years ago, unless I'm missing something.

Might have to push this to 0.8.

diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/orm/properties.py
--- a/lib/sqlalchemy/orm/properties.py  Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/orm/properties.py  Sat Feb 18 08:39:33 2012 -0500
@@ -1030,7 +1030,8 @@
             # general mapped table, which in the case of inheritance is
             # a join.
             return join_condition(mapper.mapped_table, table, 
-                                        a_subset=mapper.local_table)
+                                        a_subset=mapper.local_table,
+                                        consider_as_foreign_keys=self._user_defined_foreign_keys)

         try:
             if self.secondary is not None:
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/sql/util.py
--- a/lib/sqlalchemy/sql/util.py    Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/sql/util.py    Sat Feb 18 08:39:33 2012 -0500
@@ -225,7 +225,10 @@

     return visitors.cloned_traverse(crit, {}, {'binary':visit_binary})

-def join_condition(a, b, ignore_nonexistent_tables=False, a_subset=None):
+def join_condition(a, b, 
+                    ignore_nonexistent_tables=False, 
+                    a_subset=None,
+                    consider_as_foreign_keys=None):
     """create a join condition between two tables or selectables.

     e.g.::
@@ -269,6 +272,9 @@
                 else:
                     continue

+            if consider_as_foreign_keys and \
+                fk.parent not in consider_as_foreign_keys:
+                continue
             if col is not None:
                 crit.append(col == fk.parent)
                 constraints.add(fk.constraint)
@@ -286,6 +292,9 @@
                         # coverage to mark it.
                         continue

+                if consider_as_foreign_keys and \
+                    fk.parent not in consider_as_foreign_keys:
+                    continue
                 if col is not None:
                     crit.append(col == fk.parent)
                     constraints.add(fk.constraint)
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 test/orm/test_relationships.py
--- a/test/orm/test_relationships.py    Thu Feb 16 19:29:00 2012 -0500
+++ b/test/orm/test_relationships.py    Sat Feb 18 08:39:33 2012 -0500
@@ -202,7 +202,7 @@
             'company':relationship(Company, backref='employees'),
             'reports_to':relationship(Employee,
                 remote_side=[employee_t.c.company_id](employee_t.c.emp_id,),
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })
@@ -240,7 +240,7 @@
                         (employee_t.c.reports_to_id, employee_t.c.emp_id),
                         (employee_t.c.company_id, employee_t.c.company_id)
                 ],
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })

works great for the usual case of multiple foreign keys:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Project(Base):
    __tablename__ = 'project'
    id      = Column(String(100), primary_key=True)
    client_id = Column(String(80), ForeignKey('user.username'))
    creator_id = Column(String(80), ForeignKey('user.username'))
    workers = relationship("User", backref="assigned_project", 
                                foreign_keys="User.project_id",
                                )
    client  = relationship("User", backref="owned_projects", 
                                foreign_keys=client_id,
                                post_update=True)
    creator = relationship("User", backref="created_projects", 
                                foreign_keys=creator_id,
                                post_update=True)

class User(Base):
    __tablename__ = 'user'
    username            = Column(String(80),  primary_key=True)
    project_id = Column(String(100), ForeignKey("project.id", use_alter=True,
                                        name="fk_user_project_id"))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)

p1 = Project(id="someproject")
u1 = User(username="someuser")
p1.client = p1.creator = u1
u1.assigned_project = p1
s.add(u1)
s.commit()

Comments (2)