-----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 [email protected].
To unsubscribe from this group, send email to
[email protected].
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
