I'm trying to make a patch to make join work with many-to-many relationships via an association table. This works for something like: a join b, but is having ambiguous column name problems if you attempt to do something like: a join b join c.
Patch attached as well as a test script illustrating what works and what not.
Any ideas Mike? Would this be useful?
--
---------------------------------------------------------------------------------------------------
John S. Yang
from sqlalchemy import *
db = engine.create_engine('sqlite://filename=:memory:', echo=True, default_ordering=True)
a = Table('a', db,
Column('id', Integer, Sequence('a_id_seq', optional=True), primary_key=True),
Column('name', String(30), nullable=False),
)
b = Table('b', db,
Column('id', Integer, Sequence('b_id_seq', optional=True), primary_key=True),
Column('name', String(30)),
)
c = Table('c', db,
Column('id', Integer, Sequence('c_id_seq', optional=True), primary_key=True),
Column('name', String(30)),
)
a_x_b = Table('a_x_b', db,
Column('a_id', Integer, ForeignKey('a.id')),
Column('b_id', Integer, ForeignKey('b.id')),
)
a_x_c = Table('a_x_c', db,
Column('a_id', Integer, ForeignKey('a.id')),
Column('c_id', Integer, ForeignKey('c.id')),
)
for t in [a, b, c, a_x_b, a_x_c]:
t.create()
# This works
a.join(b, middle=a_x_b).select().execute()
# This not works, complains about:
# ...
# File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.1.3-py2.4.egg/sqlalchemy/engine.py", line 649, in _execute
# raise exceptions.SQLError(statement, parameters, e)
# sqlalchemy.exceptions.SQLError: (OperationalError) no such column: a_x_b.b_id
a.join(b, middle=a_x_b).join(c, middle=a_x_c).select().execute()
Index: lib/sqlalchemy/sql.py
===================================================================
--- lib/sqlalchemy/sql.py (revision 1102)
+++ lib/sqlalchemy/sql.py (working copy)
@@ -835,14 +835,17 @@
class Join(FromClause):
- def __init__(self, left, right, onclause=None, isouter = False):
+ def __init__(self, left, right, onclause=None, isouter = False,
middle=None):
self.left = left
self.right = right
self.id = self.left.id + "_" + self.right.id
# TODO: if no onclause, do NATURAL JOIN
if onclause is None:
- self.onclause = self._match_primaries(left, right)
+ if middle:
+ self.onclause = self._match_association(left, right, middle)
+ else:
+ self.onclause = self._match_primaries(left, right)
else:
self.onclause = onclause
self.isouter = isouter
@@ -858,6 +861,20 @@
if column.foreign_key:
self._foreign_keys.append(column.foreign_key)
return column
+ def _match_association(self, left, right, middle):
+ crit = []
+ for fk in middle.foreign_keys:
+ if fk.references(left):
+ crit.append(left._get_col_by_original(fk.column) == fk.parent)
+ if fk.references(right):
+ crit.append(right._get_col_by_original(fk.column) == fk.parent)
+ if len(crit) == 0:
+ raise ArgumentError("Cant find many-to-many relationships between
'%s' and '%s' with association '%s'" % (primary.name, secondary.name,
middle.name))
+ elif len(crit) == 1:
+ return (crit[0])
+ else:
+ return and_(*crit)
+
def _match_primaries(self, primary, secondary):
crit = []
for fk in secondary.foreign_keys:
Index: lib/sqlalchemy/engine.py
===================================================================
--- lib/sqlalchemy/engine.py (revision 1102)
+++ lib/sqlalchemy/engine.py (working copy)
@@ -687,7 +687,7 @@
def __init__(self, key):
self.key = key
def convert_result_value(self, arg, engine):
- raise InvalidRequestError("Ambiguous column name '%s' in result
set! try 'use_labels' option on select statement." % (self.key))
+ raise exceptions.InvalidRequestError("Ambiguous column name '%s'
in result set! try 'use_labels' option on select statement." % (self.key))
def __init__(self, cursor, engine, typemap = None):
"""ResultProxy objects are constructed via the execute() method on
SQLEngine."""
