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/elements.py | 65 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 65 insertions(+) (limited to 'lib/sqlalchemy/sql/elements.py') 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). -- 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/elements.py | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'lib/sqlalchemy/sql/elements.py') 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')". -- 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/elements.py') 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/elements.py') 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/elements.py') 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