From a57f7c072b15f9af5c1d66abd0a85a502a0c4bc2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 21 Mar 2010 18:45:54 -0400 Subject: [PATCH] initial subq implementation --- lib/sqlalchemy/orm/__init__.py | 8 +++ lib/sqlalchemy/orm/properties.py | 10 ++- lib/sqlalchemy/orm/strategies.py | 106 ++++++++++++++++++++++++++-- test/orm/test_subquery_relations.py | 75 ++++++++++++++++++++ 4 files changed, 192 insertions(+), 7 deletions(-) create mode 100644 test/orm/test_subquery_relations.py diff --git a/lib/sqlalchemy/orm/__init__.py b/lib/sqlalchemy/orm/__init__.py index 3337287d88..c9ed3cf2e5 100644 --- a/lib/sqlalchemy/orm/__init__.py +++ b/lib/sqlalchemy/orm/__init__.py @@ -96,6 +96,8 @@ __all__ = ( 'relation', 'scoped_session', 'sessionmaker', + 'subqueryload', + 'subqueryload_all', 'synonym', 'undefer', 'undefer_group', @@ -974,6 +976,12 @@ def eagerload_all(*keys, **kw): else: return strategies.EagerLazyOption(keys, lazy=False, chained=True) +def subqueryload(*keys): + return strategies.EagerLazyOption(keys, _strategy_cls=strategies.SubqueryLoader) + +def subqueryload_all(*keys): + return strategies.EagerLazyOption(keys, _strategy_cls=strategies.SubqueryLoader, chained=True) + @sa_util.accepts_a_list_as_starargs(list_deprecation='deprecated') def lazyload(*keys): """Return a ``MapperOption`` that will convert the property of the given diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py index 5c43fd3555..4b6770861c 100644 --- a/lib/sqlalchemy/orm/properties.py +++ b/lib/sqlalchemy/orm/properties.py @@ -397,13 +397,17 @@ class RelationshipProperty(StrategizedProperty): elif self.lazy == 'dynamic': from sqlalchemy.orm import dynamic self.strategy_class = dynamic.DynaLoader - elif self.lazy is False: + elif self.lazy is False or self.lazy == 'joined': self.strategy_class = strategies.EagerLoader - elif self.lazy is None: + elif self.lazy is None or self.lazy == 'noload': self.strategy_class = strategies.NoLoader + elif self.lazy is False or self.lazy == 'select': + self.strategy_class = strategies.LazyLoader + elif self.lazy == 'subquery': + self.strategy_class = strategies.SubqueryLoader else: self.strategy_class = strategies.LazyLoader - + self._reverse_property = set() if cascade is not False: diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py index ce19667c69..f6b36f557e 100644 --- a/lib/sqlalchemy/orm/strategies.py +++ b/lib/sqlalchemy/orm/strategies.py @@ -17,6 +17,7 @@ from sqlalchemy.orm.interfaces import ( ) from sqlalchemy.orm import session as sessionlib from sqlalchemy.orm import util as mapperutil +import itertools def _register_attribute(strategy, mapper, useobject, compare_function=None, @@ -575,6 +576,98 @@ class LoadLazyAttribute(object): else: return None +class SubqueryLoader(AbstractRelationshipLoader): + def init_class_attribute(self, mapper): + self.parent_property._get_strategy(LazyLoader).init_class_attribute(mapper) + + def setup_query(self, context, entity, path, adapter, + column_collection=None, parentmapper=None, **kwargs): + + if not context.query._enable_eagerloads: + return + + path = path + (self.key,) + + # TODO: shouldn't have to use getattr() to get at + # InstrumentedAttributes, or alternatively should not need to + # use InstrumentedAttributes with the Query at all (it should accept + # the MapperProperty objects as well). + + local_cols, remote_cols = self._local_remote_columns + + local_attr = [getattr(self.parent.class_, key) + for key in + [self.parent._get_col_to_prop(c).key for c in local_cols] + ] + + attr = getattr(self.parent.class_, self.key) + + # modify the query to just look for parent columns in the join condition + + # TODO. secondary is not supported at all yet. + + # TODO: what happens to options() in the parent query ? are they going + # to get in the way here ? + + q = context.query._clone() + q._set_entities(local_attr) + if self.parent_property.secondary is not None: + q = q.from_self(self.mapper, *local_attr) + else: + q = q.from_self(self.mapper) + q = q.join(attr) + + if self.parent_property.secondary is not None: + q = q.order_by(*local_attr) + else: + q = q.order_by(*remote_cols) + + if self.parent_property.order_by: + q = q.order_by(*self.parent_property.order_by) + + context.attributes[('subquery', path)] = q + + @property + def _local_remote_columns(self): + if self.parent_property.secondary is None: + return zip(*self.parent_property.local_remote_pairs) + else: + return \ + [p[0] for p in self.parent_property.synchronize_pairs],\ + [p[0] for p in self.parent_property.secondary_synchronize_pairs] + + def create_row_processor(self, context, path, mapper, row, adapter): + path = path + (self.key,) + + local_cols, remote_cols = self._local_remote_columns + + local_attr = [self.parent._get_col_to_prop(c).key for c in local_cols] + remote_attr = [self.mapper._get_col_to_prop(c).key for c in remote_cols] + + q = context.attributes[('subquery', path)] + + if self.parent_property.secondary is not None: + collections = dict((k, [v[0] for v in v]) for k, v in itertools.groupby( + q, + lambda x:x[1:] + )) + else: + collections = dict((k, list(v)) for k, v in itertools.groupby( + q, + lambda x:tuple([getattr(x, key) for key in remote_attr]) + )) + + + def execute(state, dict_, row): + collection = collections.get( + tuple([row[col] for col in local_cols]), + () + ) + state.get_impl(self.key).set_committed_value(state, dict_, collection) + + return (execute, None) + + class EagerLoader(AbstractRelationshipLoader): """Strategize a relationship() that loads within the process of the parent object being selected.""" @@ -809,24 +902,29 @@ class EagerLoader(AbstractRelationshipLoader): log.class_logger(EagerLoader) class EagerLazyOption(StrategizedOption): - - def __init__(self, key, lazy=True, chained=False, mapper=None, propagate_to_loaders=True): + def __init__(self, key, lazy=True, chained=False, + mapper=None, propagate_to_loaders=True, + _strategy_cls=None + ): super(EagerLazyOption, self).__init__(key, mapper) self.lazy = lazy self.chained = chained self.propagate_to_loaders = propagate_to_loaders + self.strategy_cls = _strategy_cls def is_chained(self): return not self.lazy and self.chained def get_strategy_class(self): - if self.lazy: + if self.strategy_cls: + return self.strategy_cls + elif self.lazy: return LazyLoader elif self.lazy is False: return EagerLoader elif self.lazy is None: return NoLoader - + class EagerJoinOption(PropertyOption): def __init__(self, key, innerjoin, chained=False): diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py new file mode 100644 index 0000000000..303781ad2b --- /dev/null +++ b/test/orm/test_subquery_relations.py @@ -0,0 +1,75 @@ +from sqlalchemy.test.testing import eq_, is_, is_not_ +from sqlalchemy.test import testing +from sqlalchemy.orm import backref, subqueryload, subqueryload_all +from sqlalchemy.orm import mapper, relationship, create_session, lazyload, aliased +from sqlalchemy.test.testing import eq_, assert_raises +from sqlalchemy.test.assertsql import CompiledSQL +from test.orm import _base, _fixtures + +class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): + run_inserts = 'once' + run_deletes = None + + @testing.resolve_artifact_names + def test_basic(self): + mapper(User, users, properties={ + 'addresses':relationship(mapper(Address, addresses), order_by=Address.id) + }) + sess = create_session() + + q = sess.query(User).options(subqueryload(User.addresses)) + + def go(): + eq_( + [User(id=7, addresses=[Address(id=1, email_address='jack@bean.com')])], + q.filter(User.id==7).all() + ) + + self.assert_sql_count(testing.db, go, 2) + + def go(): + eq_( + self.static.user_address_result, + q.order_by(User.id).all() + ) + self.assert_sql_count(testing.db, go, 2) + + @testing.resolve_artifact_names + def test_many_to_many(self): + mapper(Keyword, keywords) + mapper(Item, items, properties = dict( + keywords = relationship(Keyword, secondary=item_keywords, + lazy='subquery', order_by=keywords.c.id))) + + q = create_session().query(Item).order_by(Item.id) + def go(): + eq_(self.static.item_keyword_result, q.all()) + self.assert_sql_count(testing.db, go, 2) + + def go(): + eq_(self.static.item_keyword_result[0:2], + q.join('keywords').filter(Keyword.name == 'red').all()) + self.assert_sql_count(testing.db, go, 2) + + def go(): + eq_(self.static.item_keyword_result[0:2], + (q.join('keywords', aliased=True). + filter(Keyword.name == 'red')).all()) + self.assert_sql_count(testing.db, go, 2) + + + # TODO: all the tests in test_eager_relations + + # TODO: ensure state stuff works out OK, existing objects/collections + # don't get inappropriately whacked, etc. + + # TODO: subquery loading with eagerloads on those collections ??? + + # TODO: eagerloading of child objects with subquery loading on those ??? + + # TODO: lazy loads leading into subq loads ?? + + # TODO: e.g. all kinds of path combos need to be tested + + # TODO: joined table inh ! + -- 2.47.3