From ad82849bbe4ef329129204d02781f737c0c79fcb Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Wed, 10 Sep 2014 11:34:33 +0300 Subject: implementation for FILTER (WHERE ...) --- lib/sqlalchemy/sql/__init__.py | 1 + lib/sqlalchemy/sql/compiler.py | 6 ++++ lib/sqlalchemy/sql/elements.py | 65 ++++++++++++++++++++++++++++++++++++++++ lib/sqlalchemy/sql/expression.py | 4 ++- lib/sqlalchemy/sql/functions.py | 24 ++++++++++++++- 5 files changed, 98 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 4d013859c..8fbf1b536 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -19,6 +19,7 @@ from .expression import ( Selectable, TableClause, Update, + aggregatefilter, alias, and_, asc, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 5149fa4fe..6ebd61e9c 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -760,6 +760,12 @@ class SQLCompiler(Compiled): ) ) + def visit_aggregatefilter(self, aggregatefilter, **kwargs): + return "%s FILTER (WHERE %s)" % ( + aggregatefilter.func._compiler_dispatch(self, **kwargs), + aggregatefilter.criterion._compiler_dispatch(self, **kwargs) + ) + def visit_extract(self, extract, **kwargs): field = self.extract_map.get(extract.field, extract.field) return "EXTRACT(%s FROM %s)" % ( diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 8ec0aa700..5562e80d7 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2888,6 +2888,71 @@ class Over(ColumnElement): )) +class AggregateFilter(ColumnElement): + """Represent an aggregate FILTER clause. + + This is a special operator against aggregate functions, + which controls which rows are passed to it. + It's supported only by certain database backends. + + """ + __visit_name__ = 'aggregatefilter' + + criterion = None + + def __init__(self, func, *criterion): + """Produce an :class:`.AggregateFilter` object against a function. + + Used against aggregate functions, + for database backends that support aggregate "FILTER" clause. + + E.g.:: + + from sqlalchemy import aggregatefilter + aggregatefilter(func.count(1), MyClass.name == 'some name') + + Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". + + This function is also available from the :data:`~.expression.func` + construct itself via the :meth:`.FunctionElement.filter` method. + + """ + self.func = func + self.filter(*criterion) + + def filter(self, *criterion): + for criterion in list(criterion): + criterion = _expression_literal_as_text(criterion) + + if self.criterion is not None: + self.criterion = self.criterion & criterion + else: + self.criterion = criterion + + return self + + @util.memoized_property + def type(self): + return self.func.type + + def get_children(self, **kwargs): + return [c for c in + (self.func, self.criterion) + if c is not None] + + def _copy_internals(self, clone=_clone, **kw): + self.func = clone(self.func, **kw) + if self.criterion is not None: + self.criterion = clone(self.criterion, **kw) + + @property + def _from_objects(self): + return list(itertools.chain( + *[c._from_objects for c in (self.func, self.criterion) + if c is not None] + )) + + class Label(ColumnElement): """Represents a column label (AS). diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index d96f048b9..7b22cab3e 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -36,7 +36,7 @@ from .elements import ClauseElement, ColumnElement,\ True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \ Grouping, not_, \ collate, literal_column, between,\ - literal, outparam, type_coerce, ClauseList + literal, outparam, type_coerce, ClauseList, AggregateFilter from .elements import SavepointClause, RollbackToSavepointClause, \ ReleaseSavepointClause @@ -97,6 +97,8 @@ outerjoin = public_factory(Join._create_outerjoin, ".expression.outerjoin") insert = public_factory(Insert, ".expression.insert") update = public_factory(Update, ".expression.update") delete = public_factory(Delete, ".expression.delete") +aggregatefilter = public_factory( + AggregateFilter, ".expression.aggregatefilter") # internal functions still being called from tests and the ORM, diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 7efb1e916..46f3e27dc 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -12,7 +12,7 @@ from . import sqltypes, schema from .base import Executable, ColumnCollection from .elements import ClauseList, Cast, Extract, _literal_as_binds, \ literal_column, _type_from_args, ColumnElement, _clone,\ - Over, BindParameter + Over, BindParameter, AggregateFilter from .selectable import FromClause, Select, Alias from . import operators @@ -116,6 +116,28 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return Over(self, partition_by=partition_by, order_by=order_by) + def filter(self, *criterion): + """Produce a FILTER clause against this function. + + Used against aggregate functions, + for database backends that support aggregate "FILTER" clause. + + The expression:: + + func.count(1).filter(True) + + is shorthand for:: + + from sqlalchemy import aggregatefilter + aggregatefilter(func.count(1), True) + + See :func:`~.expression.aggregatefilter` for a full description. + + """ + if not criterion: + return self + return AggregateFilter(self, *criterion) + @property def _from_objects(self): return self.clauses._from_objects -- cgit v1.2.1 From ab1c25266dd49f087b5fff316b6ba6fb610b1d35 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:29:33 +0300 Subject: renamed aggregatefilter to funcfilter, since it is that --- lib/sqlalchemy/sql/__init__.py | 2 +- lib/sqlalchemy/sql/compiler.py | 6 +++--- lib/sqlalchemy/sql/elements.py | 18 +++++++++--------- lib/sqlalchemy/sql/expression.py | 6 +++--- lib/sqlalchemy/sql/functions.py | 14 +++++++------- 5 files changed, 23 insertions(+), 23 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 8fbf1b536..351e08d0b 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -19,7 +19,6 @@ from .expression import ( Selectable, TableClause, Update, - aggregatefilter, alias, and_, asc, @@ -39,6 +38,7 @@ from .expression import ( false, False_, func, + funcfilter, insert, intersect, intersect_all, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 6ebd61e9c..d59012d12 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -760,10 +760,10 @@ class SQLCompiler(Compiled): ) ) - def visit_aggregatefilter(self, aggregatefilter, **kwargs): + def visit_funcfilter(self, funcfilter, **kwargs): return "%s FILTER (WHERE %s)" % ( - aggregatefilter.func._compiler_dispatch(self, **kwargs), - aggregatefilter.criterion._compiler_dispatch(self, **kwargs) + funcfilter.func._compiler_dispatch(self, **kwargs), + funcfilter.criterion._compiler_dispatch(self, **kwargs) ) def visit_extract(self, extract, **kwargs): diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 5562e80d7..5ac16ab7a 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2888,28 +2888,28 @@ class Over(ColumnElement): )) -class AggregateFilter(ColumnElement): - """Represent an aggregate FILTER clause. +class FunctionFilter(ColumnElement): + """Represent a function FILTER clause. - This is a special operator against aggregate functions, + This is a special operator against aggregate and window functions, which controls which rows are passed to it. It's supported only by certain database backends. """ - __visit_name__ = 'aggregatefilter' + __visit_name__ = 'funcfilter' criterion = None def __init__(self, func, *criterion): - """Produce an :class:`.AggregateFilter` object against a function. + """Produce an :class:`.FunctionFilter` object against a function. - Used against aggregate functions, - for database backends that support aggregate "FILTER" clause. + Used against aggregate and window functions, + for database backends that support the "FILTER" clause. E.g.:: - from sqlalchemy import aggregatefilter - aggregatefilter(func.count(1), MyClass.name == 'some name') + from sqlalchemy import funcfilter + funcfilter(func.count(1), MyClass.name == 'some name') Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 7b22cab3e..2e10b7370 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -36,7 +36,7 @@ from .elements import ClauseElement, ColumnElement,\ True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \ Grouping, not_, \ collate, literal_column, between,\ - literal, outparam, type_coerce, ClauseList, AggregateFilter + literal, outparam, type_coerce, ClauseList, FunctionFilter from .elements import SavepointClause, RollbackToSavepointClause, \ ReleaseSavepointClause @@ -97,8 +97,8 @@ outerjoin = public_factory(Join._create_outerjoin, ".expression.outerjoin") insert = public_factory(Insert, ".expression.insert") update = public_factory(Update, ".expression.update") delete = public_factory(Delete, ".expression.delete") -aggregatefilter = public_factory( - AggregateFilter, ".expression.aggregatefilter") +funcfilter = public_factory( + FunctionFilter, ".expression.funcfilter") # internal functions still being called from tests and the ORM, diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 46f3e27dc..a07eca8c6 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -12,7 +12,7 @@ from . import sqltypes, schema from .base import Executable, ColumnCollection from .elements import ClauseList, Cast, Extract, _literal_as_binds, \ literal_column, _type_from_args, ColumnElement, _clone,\ - Over, BindParameter, AggregateFilter + Over, BindParameter, FunctionFilter from .selectable import FromClause, Select, Alias from . import operators @@ -119,8 +119,8 @@ class FunctionElement(Executable, ColumnElement, FromClause): def filter(self, *criterion): """Produce a FILTER clause against this function. - Used against aggregate functions, - for database backends that support aggregate "FILTER" clause. + Used against aggregate and window functions, + for database backends that support the "FILTER" clause. The expression:: @@ -128,15 +128,15 @@ class FunctionElement(Executable, ColumnElement, FromClause): is shorthand for:: - from sqlalchemy import aggregatefilter - aggregatefilter(func.count(1), True) + from sqlalchemy import funcfilter + funcfilter(func.count(1), True) - See :func:`~.expression.aggregatefilter` for a full description. + See :func:`~.expression.funcfilter` for a full description. """ if not criterion: return self - return AggregateFilter(self, *criterion) + return FunctionFilter(self, *criterion) @property def _from_objects(self): -- cgit v1.2.1 From 52a095ba6675f5f5807a1dc655b4ae32b9999f27 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:39:56 +0300 Subject: allow windowing filtered functions --- lib/sqlalchemy/sql/elements.py | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 5ac16ab7a..62fe6553a 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2931,6 +2931,26 @@ class FunctionFilter(ColumnElement): return self + def over(self, partition_by=None, order_by=None): + """Produce an OVER clause against this filtered function. + + Used against aggregate or so-called "window" functions, + for database backends that support window functions. + + The expression:: + + func.rank().filter(MyClass.y > 5).over(order_by='x') + + is shorthand for:: + + from sqlalchemy import over, funcfilter + over(funcfilter(func.rank(), MyClass.y > 5), order_by='x') + + See :func:`~.expression.over` for a full description. + + """ + return Over(self, partition_by=partition_by, order_by=order_by) + @util.memoized_property def type(self): return self.func.type -- cgit v1.2.1 From 89fc7d65b9ac12dd70d48c8d3be04bd50e696ce6 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:47:24 +0300 Subject: documentation indentation fix --- lib/sqlalchemy/sql/elements.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 62fe6553a..c1c4fc1e1 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2908,8 +2908,8 @@ class FunctionFilter(ColumnElement): E.g.:: - from sqlalchemy import funcfilter - funcfilter(func.count(1), MyClass.name == 'some name') + from sqlalchemy import funcfilter + funcfilter(func.count(1), MyClass.name == 'some name') Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". -- cgit v1.2.1 From 76c06aa65345b47af38a0a1d20638dfbc890b640 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:49:51 +0300 Subject: method documentation typo fix --- lib/sqlalchemy/sql/elements.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index c1c4fc1e1..53838358d 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2901,7 +2901,7 @@ class FunctionFilter(ColumnElement): criterion = None def __init__(self, func, *criterion): - """Produce an :class:`.FunctionFilter` object against a function. + """Produce a :class:`.FunctionFilter` object against a function. Used against aggregate and window functions, for database backends that support the "FILTER" clause. -- cgit v1.2.1