On Feb 17, 2010, at 10:25 AM, Manlio Perillo wrote: > -----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.
so what I had in mind is that, if its given a join as the left side, it just does the "natural" thing, i.e. joins to the right. If the "natural" join isn't available, then it does its usual search through the whole thing. I think _match_primaries could, right before it raises its message, just ask "well is this particular foreign key the rightmost join on the left side" and then its good. to get non-default behavior, as always you'd specify the on clause. which you'd have to do anyway even without the natural feature if you wanted to join....unnaturally. > > 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 > -- 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.
