def paginate(select: Select, page_position: PagePosition) -> Select: if page_position.sort: order_by_clauses = [ _sort_direction_map[order.direction](order.field) for order in page_position.sort.orders ] select = select.order_by(*order_by_clauses) return select.limit(page_position.limit).offset(page_position.offset)
def limit_query(query: Select, limit: Optional[str] = None, offset: Optional[str] = None) -> Select: if limit: validators.raise_if_not_int(limit) query = query.limit(int(limit)) if offset: validators.raise_if_not_int(offset) query = query.offset(int(offset)) return query
def paginate( query: Select, columns: Dict[Column, Any], order: Order, limit: int, ) -> Select: orderer = get_orderer(order) comparator = get_comparator(order) for column, value in columns.items(): query = query.order_by(orderer(column)) if value is not None: query = query.where(comparator(column, value)) return query.limit(limit)
def _apply_limit(query: Select, args: dict) -> Select: """ If a limit has been supplied by the 'length' parameter (and an offset supplied by the 'start' parameter) from a DataTable AJAX request, this adds it to the given query. A length of -1 represents no limit or offset. The offset index is 0-based. :param query: the DataTable SQL query :param args: the query parameters from a DataTable AJAX request :return: the query with the limit (if it exists) applied """ limit = int(args['length']) if limit != -1: offset = int(args.get('start', 0)) query = query.limit(limit).offset(offset) return query
async def get_paginator(request: Request, query: Select, columns): """columns: column, sort order, from col to url, from url to col, url pattern""" assert len(columns) > 0 need_reverse = False pattern = '_'.join([column[4] for column in columns]) if 'after' in request.raw_args and re.match(f"^{pattern}$", request.raw_args['after']): cols_vals = request.raw_args['after'].split('_') col, order, to_url, from_url, _ = columns[0] order_by = [getattr(col, order)()] if len(columns) == 1: if order == 'desc': condition = col <= from_url(cols_vals[0]) else: condition = col >= from_url(cols_vals[0]) else: col2, order2, to_url2, from_url2, _ = columns[1] order_by.append(getattr(col2, order2)()) if order2 == 'desc': condition = (col < from_url(cols_vals[0])) \ | (col == from_url(cols_vals[0])) & (col2 <= from_url2(cols_vals[1])) else: condition = (col > from_url(cols_vals[0])) \ | (col == from_url(cols_vals[0])) & (col2 >= from_url2(cols_vals[1])) query = query.where(condition).order_by(*order_by) limit = PAGE_SIZE + 2 slice_from = 1 elif 'before' in request.raw_args and re.match(f"^{pattern}$", request.raw_args['before']): cols_vals = request.raw_args['before'].split('_') col, order, to_url, from_url, _ = columns[0] order_by = [col.asc() if order == 'desc' else col.desc()] if len(columns) == 1: if order == 'desc': condition = col >= from_url(cols_vals[0]) else: condition = col <= from_url(cols_vals[0]) else: col2, order2, to_url2, from_url2, _ = columns[1] order_by.append(col2.asc() if order2 == 'desc' else col2.desc()) if order2 == 'desc': condition = (col > from_url(cols_vals[0])) \ | (col == from_url(cols_vals[0])) & (col2 >= from_url2(cols_vals[1])) else: condition = (col < from_url(cols_vals[0])) \ | (col == from_url(cols_vals[0])) & (col2 <= from_url2(cols_vals[1])) query = query.where(condition).order_by(*order_by) limit = PAGE_SIZE + 2 slice_from = 1 need_reverse = True else: col, order, _, _, _ = columns[0] order_by = [getattr(col, order)()] if len(columns) > 1: col2, order2, _, _, _ = columns[1] order_by.append(getattr(col2, order2)()) query = query.order_by(*order_by) limit = PAGE_SIZE + 1 slice_from = 0 query = query.limit(limit) all_items = await query.gino.all() items = all_items[slice_from:PAGE_SIZE + slice_from] after = before = None if need_reverse: items = list(reversed(items)) after = True if len(all_items) == limit: before = True else: if slice_from == 1: before = True if len(all_items) == limit: after = True if after: after = '_'.join( [col[2](getattr(items[-1], col[0].name)) for col in columns]) after = url_without_qs_param(request.url, 'before', {'after': after}) if before: before = '_'.join( [col[2](getattr(items[0], col[0].name)) for col in columns]) before = url_without_qs_param(request.url, 'after', {'before': before}) return Paginator(items=items, after=after, before=before)
def query( cls, select_query: Select, taxon_model_info_map: Dict[str, TaxonModelInfo], projection_taxons: SlugExprTaxonMap, data_source: str, order_by: Optional[List[TaxonDataOrder]], limit: Optional[int], offset: Optional[int], used_physical_data_sources: Set[str], dimension_templates: Optional[List[SqlFormulaTemplate]] = None, ) -> Dataframe: """ Generates the final projected dataframe :param select_query: Original query fetching all necessary fields :param taxon_model_info_map: Map of taxon slug expression to taxon model info :param projection_taxons: List of taxons meant to be projected by the final query :param data_source: Virtual data source for this subrequest :param order_by: List of clauses for order by :param limit: Limit for the query :param offset: Offset for the query :param dimension_templates: List of dimension templates :return: Final dataframe including all requested taxons """ group_by = [] selectors = [] projected_df_columns: Dict[TaxonExpressionStr, DataframeColumn] = {} for taxon in projection_taxons.values(): # apply aggregation, if you need to agg_type = taxon.tel_metadata_aggregation_type if agg_type and agg_type in cls._AGGREGATION_FUNCTIONS_MAP: col = cls._AGGREGATION_FUNCTIONS_MAP[agg_type](column(taxon.slug_safe_sql_identifier)) else: col = column(taxon.slug_safe_sql_identifier) col = col.label(taxon.slug_safe_sql_identifier) # create appropriate dataframe column value_quality_type = ValueQuantityType.scalar if not taxon.calculation and taxon.slug_expr in taxon_model_info_map: value_quality_type = taxon_model_info_map[taxon.slug_expr].quantity_type df_column_name = TaxonExpressionStr(taxon.slug) projected_df_columns[df_column_name] = DataframeColumn(df_column_name, taxon, value_quality_type) # make sure we select this column in the query selectors.append(col) # check whether this taxon should be in group by clause if agg_type in cls._GROUP_BY_AGGREGATION_TYPES: group_by.append(col) # make sure we select all columns for dimension templates for dim_template in dimension_templates or []: col = column(dim_template.label) selectors.append(col) # we should group by all dimension templates group_by.append(col) # On purpose adding this value to emulate USING ON FALSE => PROD-8136 selectors.append(literal(data_source).label(HUSKY_QUERY_DATA_SOURCE_COLUMN_NAME)) # using literal_column here because some database engines do not like grouping by constant group_by.append(literal_column(HUSKY_QUERY_DATA_SOURCE_COLUMN_NAME)) # created this query new_query = Select( columns=sort_columns(selectors), order_by=[nullslast(ORDER_BY_FUNCTIONS[item.type](item.taxon)) for item in (order_by or [])], group_by=sort_columns(group_by), ).select_from(select_query) if limit is not None: new_query = new_query.limit(limit) if offset is not None: new_query = new_query.offset(offset) # collect names of all used models used_model_names = { model_info.model_name for model_info in taxon_model_info_map.values() if model_info.model_name is not None } return Dataframe(new_query, projected_df_columns, used_model_names, used_physical_data_sources)