#26602: RawSQL window functions break count()
-------------------------------------+-------------------------------------
Reporter: daggaz | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by daggaz):
Yes, you're suggestions did work as solutions, thank you.
Inspired by your `RawAggregate` subclass, I decided I'd have a go at
writing a proper window function `Expression`. I was mostly guessing at
how the API is supposed to work, so any hints/improvements would be
welcome.
{{{#!python
class Window(Expression):
template = '%(expression)s OVER (%(window)s)'
def __init__(self, expression, partition_by=None, order_by=None,
output_field=None):
self.order_by = order_by
if isinstance(order_by, six.string_types):
if order_by.startswith('-'):
self.order_by = OrderBy(F(self.order_by[1:]),
descending=True)
else:
self.order_by = OrderBy(F(self.order_by))
self.partition_by = partition_by
if self.partition_by:
self.partition_by = self._parse_expressions(partition_by)[0]
super(Window, self).__init__(output_field=output_field)
self.source_expression = self._parse_expressions(expression)[0]
if not self.source_expression.contains_aggregate:
raise FieldError("Window function expressions must be
aggregate functions")
def _resolve_output_field(self):
if self._output_field is None:
self._output_field = self.source_expression.output_field
def resolve_expression(self, query=None, allow_joins=True, reuse=None,
summarize=False, for_save=False):
c = self.copy()
c.source_expression =
c.source_expression.resolve_expression(query, allow_joins, reuse,
summarize, for_save)
if c.partition_by:
c.partition_by = c.partition_by.resolve_expression(query,
allow_joins, reuse, summarize, for_save)
if c.order_by:
c.order_by = c.order_by.resolve_expression(query, allow_joins,
reuse, summarize, for_save)
c.is_summary = summarize
c.for_save = for_save
return c
def as_sql(self, compiler, connection, function=None, template=None):
connection.ops.check_expression_support(self)
expr_sql, params = compiler.compile(self.source_expression)
window_sql = []
if self.partition_by:
window_sql.append('PARTITION BY ')
order_sql, order_params = compiler.compile(self.partition_by)
window_sql.append(order_sql)
params.extend(order_params)
if self.order_by:
window_sql.append(' ORDER BY ')
order_sql, order_params = compiler.compile(self.order_by)
window_sql.append(order_sql)
params.extend(order_params)
template = template or self.template
return template % {'expression': expr_sql, 'window':
"".join(window_sql)}, params
def copy(self):
copy = super(Window, self).copy()
copy.source_expression = self.source_expression.copy()
copy.partition_by = self.partition_by.copy() if self.partition_by
else None
copy.order_by = self.order_by.copy() if self.order_by else None
return copy
def get_group_by_cols(self):
return []
}}}
Which means you can write things like this:
{{{#!python
A.objects.annotate(total=Window(Sum('amount'), order_by='created'))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26602#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.c31d675f2104a9adf9963ca23cc74000%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
