-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Michael Bayer ha scritto: > On Feb 17, 2010, at 9:19 AM, Manlio Perillo wrote: > > Hi. > > I have found another limitation of SQLAlchemy, when handling joins. > The code is here: > http://paste.pocoo.org/show/179286/ > > I get: > sqlalchemy.exc.ArgumentError: Can't determine join between 'Join object > on Join object on content_types(159586732) and > contents(159588044)(160166604) and content_article(159601292)' and > 'categories'; tables have more than one foreign key constraint > relationship between them. Please specify the 'onclause' of this join > explicitly. > > > However, again, a plain SQL NATURAL JOIN has no problems figuring out > how to do the join. > > How hard is to improve the util.join_condition function? > > In the code I posted, it is rather obvious how to do the join, since > only one of the two foreign key constraint in the `content_article` > table should be used for the join with the `categories` table. > Instead SQLAlchemy is considering *all* foreign key constraints, even > the ones that don't involve the two tables being joined. > >> it definitely only considers foreign keys that join between the left and >> right sides.
Ah, you are right, sorry: col = fk.get_referent(a) >> if the left side is itself a join, all columns which reference or are referenced by the right are considered. >> the difference with NATURAL JOIN is that it is specifically against the rightmost side of the left chain. >> Feel free to implement this for join_condition assuming test coverage can be maintained. What do you think about the attahed patch? Note that if the dialect supports NATURAL JOIN, SQLAlchemy could use it. I have not implemented this in the patch; it should not be hard however. With the patch, my code works: join = sql.join( content_types, contents, isnatural=True ).join(content_article, isnatural=True ).join(categories, isnatural=True) and it does not change current code, since natural join is disabled by default. Manlio -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt8CmYACgkQscQJ24LbaUQl8QCdEhkz2fGnNnjw98YWtkfW91Tp oKsAn3u0kLnkJ6m3zsez+atCAWeobBuD =bZU+ -----END PGP SIGNATURE-----
-- 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.
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -96,7 +96,7 @@ """ return Join(left, right, onclause, isouter=True) -def join(left, right, onclause=None, isouter=False): +def join(left, right, onclause=None, isouter=False, isnatural=False): """Return a ``JOIN`` clause element (regular inner join). The returned object is an instance of :class:`Join`. @@ -119,7 +119,7 @@ methods on the resulting :class:`Join` object. """ - return Join(left, right, onclause, isouter) + return Join(left, right, onclause, isouter, isnatural) def select(columns=None, whereclause=None, from_obj=[], **kwargs): """Returns a ``SELECT`` clause element. @@ -1917,10 +1917,10 @@ return select([self], whereclause, **params) - def join(self, right, onclause=None, isouter=False): + def join(self, right, onclause=None, isouter=False, isnatural=False): """return a join of this :class:`FromClause` against another :class:`FromClause`.""" - return Join(self, right, onclause, isouter) + return Join(self, right, onclause, isouter, isnatural) def outerjoin(self, right, onclause=None): """return an outer join of this :class:`FromClause` against another :class:`FromClause`.""" @@ -2753,12 +2753,17 @@ """ __visit_name__ = 'join' - def __init__(self, left, right, onclause=None, isouter=False): + def __init__(self, left, right, onclause=None, isouter=False, isnatural=False): self.left = _literal_as_text(left) self.right = _literal_as_text(right).self_group() if onclause is None: - self.onclause = self._match_primaries(self.left, self.right) + left = self.left + if isnatural: + while isinstance(left, Join): + left = left.right + + self.onclause = self._match_primaries(left, self.right) else: self.onclause = onclause