From 2a77850707db1dc71bd069be04b049cc52af8983 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 2 Mar 2007 21:22:14 +0000 Subject: [PATCH] - added "fold_equivalents" argument to Join.select(), which removes 'duplicate' columns from the resulting column clause that are known to be equivalent based on the join condition. this is of great usage when constructing subqueries of joins which Postgres complains about if duplicate column names are present. - added support to polymorphic stuff for more than one "equivalent column", when trying to target columns in the polymorphic union; this applies to multi-leveled inheritance - put above-two concepts together to get the abc_inheritance tests to work with postgres --- CHANGES | 5 +++ doc/build/content/sqlconstruction.txt | 13 ++++++- lib/sqlalchemy/orm/mapper.py | 24 ++++++++++-- lib/sqlalchemy/orm/properties.py | 11 +++--- lib/sqlalchemy/sql.py | 53 +++++++++++++++++++++++++-- lib/sqlalchemy/sql_util.py | 5 ++- test/orm/abc_inheritance.py | 23 ++++++++---- test/orm/inheritance5.py | 4 +- 8 files changed, 113 insertions(+), 25 deletions(-) diff --git a/CHANGES b/CHANGES index 9c716568c8..6323fcbeec 100644 --- a/CHANGES +++ b/CHANGES @@ -11,6 +11,11 @@ or use literal_column("somecol").label("somelabel") - quoting wont occur for literal columns when they are "proxied" into the column collection for their selectable (is_literal flag is propigated) + - added "fold_equivalents" argument to Join.select(), which removes + 'duplicate' columns from the resulting column clause that are known to be + equivalent based on the join condition. this is of great usage when + constructing subqueries of joins which Postgres complains about if + duplicate column names are present. - orm: - a full select() construct can be passed to query.select() (which worked anyway), but also query.selectfirst(), query.selectone() which diff --git a/doc/build/content/sqlconstruction.txt b/doc/build/content/sqlconstruction.txt index 213456220b..6584f3d64d 100644 --- a/doc/build/content/sqlconstruction.txt +++ b/doc/build/content/sqlconstruction.txt @@ -13,7 +13,7 @@ For this section, we will mostly use the implcit style of execution, meaning the {python} from sqlalchemy import * - metadata = BoundMetaData('sqlite:///mydb.db', strategy='threadlocal', echo=True) + metadata = BoundMetaData('sqlite:///mydb.db', echo=True) # a table to store users users = Table('users', metadata, @@ -493,6 +493,17 @@ A join can be created on its own using the `join` or `outerjoin` functions, or c WHERE keywords.name = :keywords_name {'keywords_name': 'running'} +Joins also provide a keyword argument `fold_equivalents` on the `select()` function which allows the column list of the resulting select to be "folded" to the minimal list of columns, based on those columns that are known to be equivalent from the "onclause" of the join. This saves the effort of constructing column lists manually in conjunction with databases like Postgres which can be picky about "ambiguous columns". In this example, only the "users.user_id" column, but not the "addresses.user_id" column, shows up in the column clause of the resulting select: + + {python} + {sql}users.join(addresses).select(fold_equivalents=True).execute() + SELECT users.user_id, users.user_name, users.password, addresses.address_id, + addresses.street, addresses.city, addresses.state, addresses.zip + FROM users JOIN addresses ON users.user_id = addresses.address_id + {} + +The `fold_equivalents` argument will recursively apply to "chained" joins as well, i.e. `a.join(b).join(c)...`. + ### Table Aliases {@name=alias} Aliases are used primarily when you want to use the same table more than once as a FROM expression in a statement: diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py index 7779b99ae1..55edf0f41a 100644 --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -739,17 +739,33 @@ class Mapper(object): equated to one another. This is used when relating columns to those of a polymorphic - selectable, as the selectable usually only contains one of two + selectable, as the selectable usually only contains one of two (or more) columns that are equated to one another. + + The resulting structure is a dictionary of columns mapped + to lists of equivalent columns, i.e. + + { + tablea.col1: + [tableb.col1, tablec.col1], + tablea.col2: + [tabled.col2] + } """ result = {} def visit_binary(binary): if binary.operator == '=': - result[binary.left] = binary.right - result[binary.right] = binary.left + if binary.left in result: + result[binary.left].append(binary.right) + else: + result[binary.left] = [binary.right] + if binary.right in result: + result[binary.right].append(binary.left) + else: + result[binary.right] = [binary.left] vis = mapperutil.BinaryVisitor(visit_binary) - for mapper in self.polymorphic_iterator(): + for mapper in self.base_mapper().polymorphic_iterator(): if mapper.inherit_condition is not None: mapper.inherit_condition.accept_visitor(vis) return result diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py index cc11e193d7..98b386edd1 100644 --- a/lib/sqlalchemy/orm/properties.py +++ b/lib/sqlalchemy/orm/properties.py @@ -353,12 +353,13 @@ class PropertyLoader(StrategizedProperty): # load "polymorphic" versions of the columns present in "remote_side" - this is # important for lazy-clause generation which goes off the polymorphic target selectable for c in list(self.remote_side): - corr = self.mapper.select_table.corresponding_column(c, raiseerr=False) or \ - (c in target_equivalents and self.mapper.select_table.corresponding_column(target_equivalents[c], raiseerr=False)) - if corr: - self.remote_side.add(corr) + for equiv in [c] + (c in target_equivalents and target_equivalents[c] or []): + corr = self.mapper.select_table.corresponding_column(equiv, raiseerr=False) + if corr: + self.remote_side.add(corr) + break else: - raise exceptions.AssertionError("Could not find corresponding column for " + str(c) + " in selectable " + str(self.mapper.select_table)) + raise exceptions.AssertionError(str(self) + ": Could not find corresponding column for " + str(c) + " in selectable " + str(self.mapper.select_table)) else: self.polymorphic_primaryjoin = self.primaryjoin.copy_container() self.polymorphic_secondaryjoin = self.secondaryjoin and self.secondaryjoin.copy_container() or None diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 273af5415c..9c8d5db08d 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -1549,8 +1549,55 @@ class Join(FromClause): def _group_parenthesized(self): return True - def select(self, whereclauses = None, **params): - return select([self.left, self.right], whereclauses, from_obj=[self], **params) + def _get_folded_equivalents(self, equivs=None): + if equivs is None: + equivs = util.Set() + class LocateEquivs(ClauseVisitor): + def visit_binary(self, binary): + if binary.operator == '=' and binary.left.name == binary.right.name: + equivs.add(binary.right) + equivs.add(binary.left) + self.onclause.accept_visitor(LocateEquivs()) + collist = [] + if isinstance(self.left, Join): + left = self.left._get_folded_equivalents(equivs) + else: + left = list(self.left.columns) + if isinstance(self.right, Join): + right = self.right._get_folded_equivalents(equivs) + else: + right = list(self.right.columns) + used = util.Set() + for c in left + right: + if c in equivs: + if c.name not in used: + collist.append(c) + used.add(c.name) + else: + collist.append(c) + return collist + + def select(self, whereclause = None, fold_equivalents=False, **kwargs): + """Create a ``Select`` from this ``Join``. + + whereclause + the WHERE criterion that will be sent to the ``select()`` function + + fold_equivalents + based on the join criterion of this ``Join``, do not include equivalent + columns in the column list of the resulting select. this will recursively + apply to any joins directly nested by this one as well. + + **kwargs + all other kwargs are sent to the underlying ``select()`` function + + """ + if fold_equivalents: + collist = self._get_folded_equivalents() + else: + collist = [self.left, self.right] + + return select(collist, whereclause, from_obj=[self], **kwargs) def accept_visitor(self, visitor): self.left.accept_visitor(visitor) @@ -1912,7 +1959,7 @@ class Select(_SelectBaseMixin, FromClause): self.offset = offset self.for_update = for_update self.is_compound = False - + # indicates that this select statement should not expand its columns # into the column clause of an enclosing select, and should instead # act like a single scalar column diff --git a/lib/sqlalchemy/sql_util.py b/lib/sqlalchemy/sql_util.py index db3590cd8b..3eb4b6d06c 100644 --- a/lib/sqlalchemy/sql_util.py +++ b/lib/sqlalchemy/sql_util.py @@ -221,5 +221,8 @@ class ClauseAdapter(AbstractClauseProcessor): return None newcol = self.selectable.corresponding_column(col, raiseerr=False, keys_ok=False) if newcol is None and self.equivalents is not None and col in self.equivalents: - newcol = self.selectable.corresponding_column(self.equivalents[col], raiseerr=False, keys_ok=False) + for equiv in self.equivalents[col]: + newcol = self.selectable.corresponding_column(equiv, raiseerr=False, keys_ok=False) + if newcol: + return newcol return newcol diff --git a/test/orm/abc_inheritance.py b/test/orm/abc_inheritance.py index 2c0cfb3234..6230034a2c 100644 --- a/test/orm/abc_inheritance.py +++ b/test/orm/abc_inheritance.py @@ -8,7 +8,7 @@ def produce_test(parent, child, direction, fkeyinline): global ta, tb, tc ta = ["a", meta] ta.append(Column('id', Integer, primary_key=True)), - ta.append(Column('data', String(30))) + ta.append(Column('a_data', String(30))) if "a"== parent and direction == MANYTOONE: ta.append(Column('child_id', Integer, ForeignKey("%s.id" % child, use_alter=True, name="foo"))) elif "a" == child and direction == ONETOMANY: @@ -22,7 +22,7 @@ def produce_test(parent, child, direction, fkeyinline): tb.append(Column('id', Integer, primary_key=True)) tb.append(Column('a_id', Integer, ForeignKey("a.id"), primary_key=True)) - tb.append(Column('data', String(30))) + tb.append(Column('b_data', String(30))) if "b"== parent and direction == MANYTOONE: tb.append(Column('child_id', Integer, ForeignKey("%s.id" % child, use_alter=True, name="foo"))) @@ -37,16 +37,23 @@ def produce_test(parent, child, direction, fkeyinline): tc.append(Column('id', Integer, primary_key=True)) tc.append(Column('b_id', Integer, ForeignKey("b.id"), primary_key=True)) - tc.append(Column('data', String(30))) + tc.append(Column('c_data', String(30))) if "c"== parent and direction == MANYTOONE: tc.append(Column('child_id', Integer, ForeignKey("%s.id" % child, use_alter=True, name="foo"))) elif "c" == child and direction == ONETOMANY: tc.append(Column('parent_id', Integer, ForeignKey("%s.id" % parent, use_alter=True, name="foo"))) tc = Table(*tc) + + def tearDown(self): + if direction == MANYTOONE: + parent_table = {"a":ta, "b":tb, "c": tc}[parent] + parent_table.update(values={parent_table.c.child_id:None}).execute() + elif direction == ONETOMANY: + child_table = {"a":ta, "b":tb, "c": tc}[child] + child_table.update(values={child_table.c.parent_id:None}).execute() + super(ABCTest, self).tearDown() - # TODO: get finicky postgres to work - @testbase.supported('sqlite') def test_basic(self): parent_table = {"a":ta, "b":tb, "c": tc}[parent] child_table = {"a":ta, "b":tb, "c": tc}[child] @@ -70,14 +77,14 @@ def produce_test(parent, child, direction, fkeyinline): abcjoin = polymorphic_union( {"a":ta.select(tb.c.id==None, from_obj=[ta.outerjoin(tb, onclause=atob)]), - "b":ta.join(tb, onclause=atob).outerjoin(tc, onclause=btoc).select(tc.c.id==None), + "b":ta.join(tb, onclause=atob).outerjoin(tc, onclause=btoc).select(tc.c.id==None, fold_equivalents=True), "c":tc.join(tb, onclause=btoc).join(ta, onclause=atob) },"type", "abcjoin" ) bcjoin = polymorphic_union( { - "b":ta.join(tb, onclause=atob).outerjoin(tc, onclause=btoc).select(tc.c.id==None), + "b":ta.join(tb, onclause=atob).outerjoin(tc, onclause=btoc).select(tc.c.id==None, fold_equivalents=True), "c":tc.join(tb, onclause=btoc).join(ta, onclause=atob) },"type", "bcjoin" ) @@ -131,7 +138,7 @@ def produce_test(parent, child, direction, fkeyinline): result2 = sess.query(parent_class).get(parent2.id) assert result2.id == parent2.id assert result2.collection[0].id == child_obj.id - ABCTest.__name__ = "Test%s%s%d%sTest" % (parent, child, direction, fkeyinline) + ABCTest.__name__ = "Test%sTo%s%s%s" % (parent, child, (direction is ONETOMANY and "O2M" or "M2O"), fkeyinline) return ABCTest for parent in ["a", "b", "c"]: diff --git a/test/orm/inheritance5.py b/test/orm/inheritance5.py index 279c1d95aa..640a8d70fe 100644 --- a/test/orm/inheritance5.py +++ b/test/orm/inheritance5.py @@ -544,9 +544,7 @@ class RelationTest7(testbase.ORMTest): car_join = polymorphic_union( { - 'car' : cars.select(offroad_cars.c.car_id == None, from_obj=[cars.outerjoin(offroad_cars)]), - # cant do this one because "car_id" from both tables conflicts on pg -# 'car' : cars.outerjoin(offroad_cars).select(offroad_cars.c.car_id == None), + 'car' : cars.outerjoin(offroad_cars).select(offroad_cars.c.car_id == None, fold_equivalents=True), 'offroad' : cars.join(offroad_cars) }, "type", 'car_join') -- 2.47.2