def _query_editables(self, has_files, extensions):
inner = (
db.session.query(EditingRevision.id, EditingRevision.editable_id)
# only get revisions belonging to the correct event + editable type
.filter(
EditingRevision.editable.has(
and_(
Editable.contribution.has(
and_(
~Contribution.is_deleted,
Contribution.event_id == self.event.id,
)
),
Editable.type == self.editable_type,
)
)
)
# allow filtering by "is latest revision" later
.add_columns(
over(
func.row_number(),
partition_by=EditingRevision.editable_id,
order_by=EditingRevision.created_dt.desc(),
).label('rownum')
)
).subquery()
revision_query = (
db.session.query(EditingRevision.editable_id)
.select_entity_from(inner)
.filter(inner.c.rownum == 1) # only latest revision
)
# filter by presence (or lack of) file types
for file_type_id, present in has_files.items():
crit = self._make_revision_file_type_filter(inner, file_type_id)
if not present:
crit = ~crit
revision_query = revision_query.filter(crit)
# filter by having files with certain extensions
for file_type_id, exts in extensions.items():
ext_filter = EditingRevisionFile.file.has(File.extension.in_(exts))
revision_query = revision_query.filter(
self._make_revision_file_type_filter(inner, file_type_id, ext_filter)
)
revision_query = revision_query.subquery()
return (Editable.query
.join(revision_query, revision_query.c.editable_id == Editable.id)
.options(joinedload('contribution'))
.all())
def limit_groups(query, model, partition_by, order_by, limit=None, offset=0):
"""Limits the number of rows returned for each group
This utility allows you to apply a limit/offset to grouped rows of a query.
Note that the query will only contain the data from `model`; i.e. you cannot
add additional entities.
:param query: The original query, including filters, joins, etc.
:param model: The model class for `query`
:param partition_by: The column to group by
:param order_by: The column to order the partitions by
:param limit: The maximum number of rows for each partition
:param offset: The number of rows to skip in each partition
"""
inner = query.add_columns(over(func.row_number(), partition_by=partition_by,
order_by=order_by).label('rownum')).subquery()
query = model.query.select_entity_from(inner)
if limit:
return query.filter(offset < inner.c.rownum, inner.c.rownum <= (limit + offset))
else:
return query.filter(offset < inner.c.rownum)
def _as_selects(self, cuboid):
over_selects = order_selects = []
measure_selects = self.measure._as_selects(cuboid)
assert len(measure_selects) == 1
ms = measure_selects[0]
over_selects = [sel for over_level in self.over_levels
for sel in over_level._as_selects(cuboid)]
partition = [over_select.column_clause for over_select in
over_selects] or None
ms.dependencies.extend(over_selects)
order_selects = [sel for order_level in self.order_levels
for sel in order_level._as_selects(cuboid)]
sort_order = 'desc' if self.desc else 'asc'
order = [getattr(order_select.column_clause, sort_order)()
for order_select in order_selects] or None
ms.dependencies.extend(order_selects)
col = self.inner_agg(ms.column_clause, cuboid)
col._is_agg = self.inner_agg
over_expr = over(col, partition_by=partition, order_by=order)
over_expr._is_agg = self.agg
return [self.select_instance(cuboid, column_clause=over_expr,
name=self.name,
dependencies=measure_selects + over_selects)]
