- the full featureset of the SelectResults extension has been merged

into a new set of methods available off of Query.  These methods
all provide "generative" behavior, whereby the Query is copied
and a new one returned with additional criterion added.
The new methods include:

  filter() - applies select criterion to the query
  filter_by() - applies "by"-style criterion to the query
  avg() - return the avg() function on the given column
  join() - join to a property (or across a list of properties)
  outerjoin() - like join() but uses LEFT OUTER JOIN
  limit()/offset() - apply LIMIT/OFFSET
  range-based access which applies limit/offset:
     session.query(Foo)[3:5]
  distinct() - apply DISTINCT
  list() - evaluate the criterion and return results

no incompatible changes have been made to Query's API and no methods
have been deprecated.  Existing methods like select(), select_by(),
get(), get_by() all execute the query at once and return results
like they always did.  join_to()/join_via() are still there although
the generative join()/outerjoin() methods are easier to use.

- the return value for multiple mappers used with instances() now returns
a cartesian product of the requested list of mappers, represented
as a list of tuples.  this corresponds to the documented behavior.
So that instances match up properly, the "uniquing" is disabled when
this feature is used.
- strings and columns can also be sent to the *args of instances() where
those exact result columns will be part of the result tuples.
- query() method is added by assignmapper.  this helps with
navigating to all the new generative methods on Query.

3 files changed, 354 insertions, 97 deletions

diff --git a/lib/sqlalchemy/ext/assignmapper.py b/lib/sqlalchemy/ext/assignmapper.py
index 178f150e5..aee96f06e 100644
--- a/lib/sqlalchemy/ext/assignmapper.py
+++ b/lib/sqlalchemy/ext/assignmapper.py
@@ -34,6 +34,7 @@ def assign_mapper(ctx, class_, *args, **kwargs):
         extension = ctx.mapper_extension
     m = mapper(class_, extension=extension, *args, **kwargs)
     class_.mapper = m
+    class_.query = classmethod(lambda cls: Query(class_, session=ctx.current))
     for name in ['get', 'select', 'select_by', 'selectfirst', 'selectfirst_by', 'selectone', 'get_by', 'join_to', 'join_via', 'count', 'count_by', 'options', 'instances']:
         monkeypatch_query_method(ctx, class_, name)
     for name in ['flush', 'delete', 'expire', 'refresh', 'expunge', 'merge', 'save', 'update', 'save_or_update']:
diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index e1fa56c65..d28445be6 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -1695,6 +1695,9 @@ class _ExtensionCarrier(MapperExtension):
     def __init__(self):
         self.__elements = []
 
+    def __iter__(self):
+        return iter(self.__elements)
+        
     def insert(self, extension):
         """Insert a MapperExtension at the beginning of this ExtensionCarrier's list."""
 
@@ -1766,7 +1769,7 @@ class ExtensionOption(MapperOption):
         self.ext = ext
 
     def process_query(self, query):
-        query._insert_extension(self.ext)
+        query.extension.append(self.ext)
 
 class ClassKey(object):
     """Key a class and an entity name to a mapper, via the mapper_registry."""
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 8df5628d1..6650954e1 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -21,7 +21,6 @@ class Query(object):
         self.with_options = with_options or []
         self.select_mapper = self.mapper.get_select_mapper().compile()
         self.always_refresh = kwargs.pop('always_refresh', self.mapper.always_refresh)
-        self.order_by = kwargs.pop('order_by', self.mapper.order_by)
         self.lockmode = lockmode
         self.extension = mapper._ExtensionCarrier()
         if extension is not None:
@@ -35,12 +34,40 @@ class Query(object):
                 _get_clause.clauses.append(primary_key == sql.bindparam(primary_key._label, type=primary_key.type, unique=True))
             self.mapper._get_clause = _get_clause
         self._get_clause = self.mapper._get_clause
-        for opt in util.flatten_iterator(self.with_options):
-            opt.process_query(self)
 
-    def _insert_extension(self, ext):
-        self.extension.insert(ext)
+        self._order_by = kwargs.pop('order_by', False)
+        self._distinct = kwargs.pop('distinct', False)
+        self._offset = kwargs.pop('offset', None)
+        self._limit = kwargs.pop('limit', None)
+        self._criterion = None
+        self._joinpoint = self.mapper
+        self._from_obj = [self.table]
 
+        for opt in util.flatten_iterator(self.with_options):
+            opt.process_query(self)
+        
+    def _clone(self):
+        q = Query.__new__(Query)
+        q.mapper = self.mapper
+        q.select_mapper = self.select_mapper
+        q._order_by = self._order_by
+        q._distinct = self._distinct
+        q.always_refresh = self.always_refresh
+        q.with_options = list(self.with_options)
+        q._session = self.session
+        q.is_polymorphic = self.is_polymorphic
+        q.lockmode = self.lockmode
+        q.extension = mapper._ExtensionCarrier()
+        for ext in self.extension:
+            q.extension.append(ext)
+        q._offset = self._offset
+        q._limit = self._limit
+        q._get_clause = self._get_clause
+        q._from_obj = list(self._from_obj)
+        q._joinpoint = self._joinpoint
+        q._criterion = self._criterion
+        return q
+    
     def _get_session(self):
         if self._session is None:
             return self.mapper.get_session()
@@ -90,20 +117,8 @@ class Query(object):
         """Return a single object instance based on the given
         key/value criterion.
 
-        This is either the first value in the result list, or None if
-        the list is empty.
-
-        The keys are mapped to property or column names mapped by this
-        mapper's Table, and the values are coerced into a ``WHERE``
-        clause separated by ``AND`` operators.  If the local
-        property/column names dont contain the key, a search will be
-        performed against this mapper's immediate list of relations as
-        well, forming the appropriate join conditions if a matching
-        property is located.
-
-        E.g.::
-
-          u = usermapper.get_by(user_name = 'fred')
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
         """
 
         ret = self.extension.get_by(self, *args, **params)
@@ -131,6 +146,11 @@ class Query(object):
         mapper's immediate list of relations as well, forming the
         appropriate join conditions if a matching property is located.
 
+        if the located property is a column-based property, the comparison
+        value should be a scalar with an appropriate type.  If the 
+        property is a relationship-bound property, the comparison value
+        should be an instance of the related class.
+
         E.g.::
 
           result = usermapper.select_by(user_name = 'fred')
@@ -145,61 +165,13 @@ class Query(object):
         """Return a ``ClauseElement`` representing the ``WHERE``
         clause that would normally be sent to ``select_whereclause()``
         by ``select_by()``.
-        """
 
-        return self._join_by(args, params)
-
-    def _join_by(self, args, params, start=None):
-        """Return a ``ClauseElement`` representing the ``WHERE``
-        clause that would normally be sent to ``select_whereclause()``
-        by ``select_by()``.
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
         """
 
-        clause = None
-        for arg in args:
-            if clause is None:
-                clause = arg
-            else:
-                clause &= arg
+        return self._join_by(args, params)
 
-        for key, value in params.iteritems():
-            (keys, prop) = self._locate_prop(key, start=start)
-            c = prop.compare(value) & self.join_via(keys)
-            if clause is None:
-                clause =  c
-            else:
-                clause &= c
-        return clause
-
-    def _locate_prop(self, key, start=None):
-        import properties
-        keys = []
-        seen = util.Set()
-        def search_for_prop(mapper_):
-            if mapper_ in seen:
-                return None
-            seen.add(mapper_)
-            if mapper_.props.has_key(key):
-                prop = mapper_.props[key]
-                if isinstance(prop, SynonymProperty):
-                    prop = mapper_.props[prop.name]
-                if isinstance(prop, properties.PropertyLoader):
-                    keys.insert(0, prop.key)
-                return prop
-            else:
-                for prop in mapper_.props.values():
-                    if not isinstance(prop, properties.PropertyLoader):
-                        continue
-                    x = search_for_prop(prop.mapper)
-                    if x:
-                        keys.insert(0, prop.key)
-                        return x
-                else:
-                    return None
-        p = search_for_prop(start or self.mapper)
-        if p is None:
-            raise exceptions.InvalidRequestError("Cant locate property named '%s'" % key)
-        return [keys, p]
 
     def join_to(self, key):
         """Given the key name of a property, will recursively descend
@@ -236,6 +208,9 @@ class Query(object):
         """Like ``select_by()``, but only return the first result by
         itself, or None if no objects returned.  Synonymous with
         ``get_by()``.
+
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
         """
 
         return self.get_by(*args, **params)
@@ -243,6 +218,9 @@ class Query(object):
     def selectone_by(self, *args, **params):
         """Like ``selectfirst_by()``, but throws an error if not
         exactly one result was returned.
+
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
         """
 
         ret = self.select_whereclause(self.join_by(*args, **params), limit=2)
@@ -326,15 +304,20 @@ class Query(object):
         """Given a ``WHERE`` criterion, create a ``SELECT COUNT``
         statement, execute and return the resulting count value.
         """
+        if self._criterion:
+            if whereclause is not None:
+                whereclause = sql.and_(self._criterion, whereclause)
+            else:
+                whereclause = self._criterion
+        from_obj = kwargs.pop('from_obj', self._from_obj)
+        kwargs.setdefault('distinct', self._distinct)
 
-        from_obj = kwargs.pop('from_obj', [])
         alltables = []
         for l in [sql_util.TableFinder(x) for x in from_obj]:
             alltables += l
-
+        
         if self.table not in alltables:
             from_obj.append(self.table)
-
         if self._nestable(**kwargs):
             s = sql.select([self.table], whereclause, **kwargs).alias('getcount').count()
         else:
@@ -361,14 +344,201 @@ class Query(object):
         """Return a new Query object, applying the given list of
         MapperOptions.
         """
-
-        return Query(self.mapper, self._session, with_options=args)
+        q = self._clone()
+        for opt in util.flatten_iterator(args):
+            q.with_options.append(opt)
+            opt.process_query(q)
+        for opt in util.flatten_iterator(self.with_options):
+            opt.process_query(self)
+        return q
 
     def with_lockmode(self, mode):
         """Return a new Query object with the specified locking mode."""
+        q = self._clone()
+        q.lockmode = mode
+        return q
+    
+    def filter(self, criterion):
+        """apply the given filtering criterion to the query and return the newly resulting ``Query``
+        
+        the criterion is any sql.ClauseElement applicable to the WHERE clause of a select.
+        """
+        q = self._clone()
+        if q._criterion is not None:
+            q._criterion = q._criterion & criterion
+        else:
+            q._criterion = criterion
+        return q
+
+    def filter_by(self, *args, **kwargs):
+        """apply the given filtering criterion to the query and return the newly resulting ``Query``
+
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
+        """
+        return self.filter(self._join_by(args, kwargs, start=self._joinpoint))
+
+    def _join_to(self, prop, outerjoin=False):
+        if isinstance(prop, list):
+            mapper = self._joinpoint
+            keys = []
+            for key in prop:
+                p = mapper.props[key]
+                keys.append(key)
+                mapper = p.mapper
+        else:
+            [keys,p] = self._locate_prop(prop, start=self._joinpoint)
+        clause = self._from_obj[-1]
+        mapper = self._joinpoint
+        for key in keys:
+            prop = mapper.props[key]
+            if outerjoin:
+                clause = clause.outerjoin(prop.select_table, prop.get_join(mapper))
+            else:
+                clause = clause.join(prop.select_table, prop.get_join(mapper))
+            mapper = prop.mapper
+        return (clause, mapper)
+
+    def _join_by(self, args, params, start=None):
+        """Return a ``ClauseElement`` representing the ``WHERE``
+        clause that would normally be sent to ``select_whereclause()``
+        by ``select_by()``.
 
-        return Query(self.mapper, self._session, lockmode=mode)
+        The criterion is constructed in the same way as the
+        ``select_by()`` method.
+        """
 
+        clause = None
+        for arg in args:
+            if clause is None:
+                clause = arg
+            else:
+                clause &= arg
+
+        for key, value in params.iteritems():
+            (keys, prop) = self._locate_prop(key, start=start)
+            c = prop.compare(value) & self.join_via(keys)
+            if clause is None:
+                clause =  c
+            else:
+                clause &= c
+        return clause
+
+    def _locate_prop(self, key, start=None):
+        import properties
+        keys = []
+        seen = util.Set()
+        def search_for_prop(mapper_):
+            if mapper_ in seen:
+                return None
+            seen.add(mapper_)
+            if mapper_.props.has_key(key):
+                prop = mapper_.props[key]
+                if isinstance(prop, SynonymProperty):
+                    prop = mapper_.props[prop.name]
+                if isinstance(prop, properties.PropertyLoader):
+                    keys.insert(0, prop.key)
+                return prop
+            else:
+                for prop in mapper_.props.values():
+                    if not isinstance(prop, properties.PropertyLoader):
+                        continue
+                    x = search_for_prop(prop.mapper)
+                    if x:
+                        keys.insert(0, prop.key)
+                        return x
+                else:
+                    return None
+        p = search_for_prop(start or self.mapper)
+        if p is None:
+            raise exceptions.InvalidRequestError("Cant locate property named '%s'" % key)
+        return [keys, p]
+
+    def _col_aggregate(self, col, func):
+        """Execute ``func()`` function against the given column.
+
+        For performance, only use subselect if `order_by` attribute is set.
+        """
+
+        ops = {'distinct':self._distinct, 'order_by':self._order_by, 'from_obj':self._from_obj}
+
+        if self._order_by is not False:
+            s1 = sql.select([col], self._criterion, **ops).alias('u')
+            return sql.select([func(s1.corresponding_column(col))]).scalar()
+        else:
+            return sql.select([func(col)], self._criterion, **ops).scalar()
+
+    def min(self, col):
+        """Execute the SQL ``min()`` function against the given column."""
+
+        return self._col_aggregate(col, sql.func.min)
+
+    def max(self, col):
+        """Execute the SQL ``max()`` function against the given column."""
+
+        return self._col_aggregate(col, sql.func.max)
+
+    def sum(self, col):
+        """Execute the SQL ``sum``() function against the given column."""
+
+        return self._col_aggregate(col, sql.func.sum)
+
+    def avg(self, col):
+        """Execute the SQL ``avg()`` function against the given column."""
+
+        return self._col_aggregate(col, sql.func.avg)
+    
+    def order_by(self, criterion):
+        """apply one or more ORDER BY criterion to the query and return the newly resulting ``Query``"""
+
+        q = self._clone()
+        if q._order_by is False:    
+            q._order_by = util.to_list(criterion)
+        else:
+            q._order_by.extend(util.to_list(criterion))
+        return q
+    
+    def join(self, prop):
+        """create a join of this ``Query`` object's criterion
+        to a relationship and return the newly resulting ``Query``.
+        
+        'prop' may be a string property name in which it is located
+        in the same manner as keyword arguments in ``select_by``, or
+        it may be a list of strings in which case the property is located
+        by direct traversal of each keyname (i.e. like join_via()).
+        """
+        
+        q = self._clone()
+        (clause, mapper) = self._join_to(prop, outerjoin=False)
+        q._from_obj = [clause]
+        q._joinpoint = mapper
+        return q
+
+    def outerjoin(self, prop):
+        """create a left outer join of this ``Query`` object's criterion
+        to a relationship and return the newly resulting ``Query``.
+        
+        'prop' may be a string property name in which it is located
+        in the same manner as keyword arguments in ``select_by``, or
+        it may be a list of strings in which case the property is located
+        by direct traversal of each keyname (i.e. like join_via()).
+        """
+        q = self._clone()
+        (clause, mapper) = self._join_to(prop, outerjoin=True)
+        q._from_obj = [clause]
+        q._joinpoint = mapper
+        return q
+
+    def select_from(self, from_obj):
+        """Set the `from_obj` parameter of the query.
+
+        `from_obj` is a list of one or more tables.
+        """
+
+        new = self._clone()
+        new._from_obj = from_obj
+        return new
+        
     def __getattr__(self, key):
         if (key.startswith('select_by_')):
             key = key[10:]
@@ -383,6 +553,57 @@ class Query(object):
         else:
             raise AttributeError(key)
 
+    def __getitem__(self, item):
+        if isinstance(item, slice):
+            start = item.start
+            stop = item.stop
+            if (isinstance(start, int) and start < 0) or \
+               (isinstance(stop, int) and stop < 0):
+                return list(self)[item]
+            else:
+                res = self._clone()
+                if start is not None and stop is not None:
+                    res._offset = (self._offset or 0)+ start
+                    res._limit = stop-start
+                elif start is None and stop is not None:
+                    res._limit = stop
+                elif start is not None and stop is None:
+                    res._offset = (self._offset or 0) + start
+                if item.step is not None:
+                    return list(res)[None:None:item.step]
+                else:
+                    return res
+        else:
+            return list(self[item:item+1])[0]
+
+    def limit(self, limit):
+        """Apply a ``LIMIT`` to the query."""
+
+        return self[:limit]
+
+    def offset(self, offset):
+        """Apply an ``OFFSET`` to the query."""
+
+        return self[offset:]
+
+    def distinct(self):
+        """Apply a ``DISTINCT`` to the query."""
+
+        new = self._clone()
+        new._distinct = True
+        return new
+
+    def list(self):
+        """Return the results represented by this ``Query`` as a list.
+
+        This results in an execution of the underlying query.
+        """
+
+        return list(self)
+    
+    def __iter__(self):
+        return iter(self.select_whereclause())
+
     def execute(self, clauseelement, params=None, *args, **kwargs):
         """Execute the given ClauseElement-based statement against
         this Query's session/mapper, return the resulting list of
@@ -400,9 +621,27 @@ class Query(object):
         finally:
             result.close()
 
-    def instances(self, cursor, *mappers, **kwargs):
+    def instances(self, cursor, *mappers_or_columns, **kwargs):
         """Return a list of mapped instances corresponding to the rows
         in a given *cursor* (i.e. ``ResultProxy``).
+        
+        *mappers_or_columns is an optional list containing one or more of
+        classes, mappers, strings or sql.ColumnElements which will be
+        applied to each row and added horizontally to the result set,
+        which becomes a list of tuples. The first element in each tuple
+        is the usual result based on the mapper represented by this
+        ``Query``. Each additional element in the tuple corresponds to an
+        entry in the *mappers_or_columns list.
+        
+        For each element in *mappers_or_columns, if the element is 
+        a mapper or mapped class, an additional class instance will be 
+        present in the tuple.  If the element is a string or sql.ColumnElement, 
+        the corresponding result column from each row will be present in the tuple.
+        
+        Note that when *mappers_or_columns is present, "uniquing" for the result set
+        is *disabled*, so that the resulting tuples contain entities as they actually
+        correspond.  this indicates that multiple results may be present if this 
+        option is used.
         """
 
         self.__log_debug("instances()")
@@ -411,25 +650,36 @@ class Query(object):
 
         context = SelectionContext(self.select_mapper, session, self.extension, with_options=self.with_options, **kwargs)
 
-        result = util.UniqueAppender([])
-        if mappers:
-            otherresults = []
-            for m in mappers:
-                otherresults.append(util.UniqueAppender([]))
-
+        process = []
+        if mappers_or_columns:
+            for m in mappers_or_columns:
+                if isinstance(m, type):
+                    m = mapper.class_mapper(m)
+                if isinstance(m, mapper.Mapper):
+                    appender = []
+                    def proc(context, row):
+                        m._instance(context, row, appender)
+                    process.append((proc, appender))
+                elif isinstance(m, sql.ColumnElement) or isinstance(m, basestring):
+                    res = []
+                    def proc(context, row):
+                        res.append(row[m])
+                    process.append((proc, res))
+            result = []
+        else:
+            result = util.UniqueAppender([])
+                    
         for row in cursor.fetchall():
             self.select_mapper._instance(context, row, result)
-            i = 0
-            for m in mappers:
-                m._instance(context, row, otherresults[i])
-                i+=1
+            for proc in process:
+                proc[0](context, row)
 
         # store new stuff in the identity map
         for value in context.identity_map.values():
             session._register_persistent(value)
 
-        if mappers:
-            return [result.data] + [o.data for o in otherresults]
+        if mappers_or_columns:
+            return zip(*([result] + [o[1] for o in process]))
         else:
             return result.data
 
@@ -491,11 +741,14 @@ class Query(object):
         statement suitable for usage in the execute() method.
         """
 
+        if self._criterion:
+            whereclause = sql.and_(self._criterion, whereclause)
+
         if whereclause is not None and self.is_polymorphic:
             # adapt the given WHERECLAUSE to adjust instances of this query's mapped table to be that of our select_table,
             # which may be the "polymorphic" selectable used by our mapper.
             whereclause.accept_visitor(sql_util.ClauseAdapter(self.table))
-
+            
         context = kwargs.pop('query_context', None)
         if context is None:
             context = QueryContext(self, kwargs)
@@ -506,7 +759,7 @@ class Query(object):
         limit = context.limit
         offset = context.offset
         if order_by is False:
-            order_by = self.order_by
+            order_by = self.mapper.order_by
         if order_by is False:
             if self.table.default_order_by() is not None:
                 order_by = self.table.default_order_by()
@@ -579,12 +832,12 @@ class QueryContext(OperationContext):
 
     def __init__(self, query, kwargs):
         self.query = query
-        self.order_by = kwargs.pop('order_by', False)
-        self.from_obj = kwargs.pop('from_obj', [])
+        self.order_by = kwargs.pop('order_by', query._order_by)
+        self.from_obj = kwargs.pop('from_obj', query._from_obj)
         self.lockmode = kwargs.pop('lockmode', query.lockmode)
-        self.distinct = kwargs.pop('distinct', False)
-        self.limit = kwargs.pop('limit', None)
-        self.offset = kwargs.pop('offset', None)
+        self.distinct = kwargs.pop('distinct', query._distinct)
+        self.limit = kwargs.pop('limit', query._limit)
+        self.offset = kwargs.pop('offset', query._offset)
         self.eager_loaders = util.Set([x for x in query.mapper._eager_loaders])
         self.statement = None
         super(QueryContext, self).__init__(query.mapper, query.with_options, **kwargs)