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)]