def get_statements(self, model_id, date, offset=0, limit=None, sort_by=None, stmt_types=None, min_belief=None, max_belief=None): """Load the statements by model and date. Parameters ---------- model_id : str The standard name of the model to get statements for. date : str The date when the model was generated. offset : int The offset to start at. limit : int The number of statements to return. Returns ------- list[indra.statements.Statement] A list of statements corresponding to the model and date. """ logger.info(f'Got request to get statements for model {model_id} ' f'on date {date} with offset {offset} and limit {limit} ' f'and sort by {sort_by}') with self.get_session() as sess: q = sess.query(Statement.statement_json).filter( Statement.model_id == model_id, Statement.date == date ) if stmt_types: stmt_types = [stmt_type.lower() for stmt_type in stmt_types] q = q.filter( func.lower(Statement.statement_json[ 'type'].astext).in_(stmt_types)) if min_belief: q = q.filter( Statement.statement_json['belief'].astext.cast( Float) >= float(min_belief)) if max_belief: q = q.filter( Statement.statement_json['belief'].astext.cast( Float) <= float(max_belief)) if sort_by == 'evidence': q = q.order_by(nullslast(jsonb_array_length( Statement.statement_json["evidence"]).desc())) elif sort_by == 'belief': q = q.order_by( nullslast(Statement.statement_json['belief'].desc())) elif sort_by == 'paths': q = q.order_by(Statement.path_count.desc()) if offset: q = q.offset(offset) if limit: q = q.limit(limit) stmts = stmts_from_json([s for s, in q.all()]) logger.info(f'Got {len(stmts)} statements') return stmts
def ordered_by_rating(limit: int = None, direction: Literal['asc', 'desc'] = 'desc'): query = Game.query\ .join(Rating, Rating.game_id == Game.id, isouter=True)\ .join(GamePlay, GamePlay.game_id == Game.id, isouter=True)\ .group_by(Game.id)\ .order_by(nullslast(desc(func.avg(Rating.rating))), nullslast(desc(func.count(GamePlay.id))), asc(Game.created_at)) if limit is not None: query = query.limit(limit) return query
def get_events_for_place(place_id: UUID, event_types: List[str] = [], include_null_type: bool = True, limit: int = None, offset: int = None) -> tuple: """Get all events that occurred at a place. Args: place_id: MBID of the place. event_types: List of types of events to be fetched. The supported event_types are 'Concert', 'Festival', 'Convention/Expo', 'Launch event', 'Award ceremony', 'Stage performance', and 'Masterclass/Clinic'. include_null_type: Whether to include events with no type. limit: Max number of events to return. offset: Offset that can be used in conjunction with the limit. Returns: Tuple containing the list of dictionaries of events and the total count of the events. The list of dictionaries of events is ordered by event begin year, begin month, begin date begin time, and begin name. In case one of these is set to NULL, it will be ordered last. """ place_id = str(place_id) event_types = get_mapped_event_types(event_types) with mb_session() as db: event_query = db.query(models.Event).outerjoin(models.EventType).\ options(contains_eager(models.Event.type)).\ join(models.LinkEventPlace, models.Event.id == models.LinkEventPlace.entity0_id).\ join(models.Place, models.LinkEventPlace.entity1_id == models.Place.id).\ filter(models.Place.gid == place_id) if include_null_type and event_types: event_query = event_query.filter( or_(models.Event.type == None, models.EventType.name.in_(event_types))) elif event_types: event_query = event_query.filter( models.EventType.name.in_(event_types)) event_query = event_query.order_by( nullslast(models.Event.begin_date_year.desc()), nullslast(models.Event.begin_date_month.desc()), nullslast(models.Event.begin_date_day.desc()), nullslast(models.Event.time.desc()), nullslast(models.Event.name.asc())) count = event_query.count() events = event_query.limit(limit).offset(offset).all() return ([serialize_events(event) for event in events], count)
def index(): new_release = Game.query.order_by(desc(Game.id)).limit(5).all() most_popular = Game.query.join(owned_games).group_by(Game.id).order_by( func.count(Game.id)).limit(5).all() coming_soon = Game.query.filter_by(status="coming soon").limit(5).all() top_rated = Game.query.order_by(nullslast(desc( Game.rating))).limit(4).all() limited_offer = Game.query.filter( Game.discount_expirable == True, Game.discount_end_date > datetime.now(), Game.discount > 0).order_by(Game.discount_end_date).limit(4).all() discount = Game.query.filter(Game.discount_expirable == False, Game.discount > 0).limit(4).all() featured_games_setting = WebsiteSetting.query.filter_by( setting_group="featured_games").all() featured_games = [] for i in featured_games_setting: featured_games.append(Game.query.filter_by(id=i.setting_value).first()) return render_template("index.html", active_page="index", new_release=new_release, most_popular=most_popular, coming_soon=coming_soon, top_rated=top_rated, limited_offer=limited_offer, discount=discount, featured_games=featured_games)
def query_agps( self, page, symbol_like=None, sort_by=None, order=None, force_view=False): if force_view: table = self.agp_view elif self.cache_table_exists(): table = self.cache_table else: table = self.agp_view s = table.select() if symbol_like: s = s.where(table.c.symbol_name.like(f"%{symbol_like}%")) if sort_by is not None: if order is None: order = "desc" sort_by = self._transform_sort_by(sort_by) query_order_func = desc if order == "desc" else asc s = s.order_by(nullslast(query_order_func(sort_by))) if page is not None: s = s.limit(self.PAGE_SIZE).offset(self.PAGE_SIZE*(page-1)) with self.engine.connect() as connection: return connection.execute(s).fetchall()
async def list(user_id: str): e = Exercise.__table__ w = Workout.__table__ query = Select(columns=[*e.c, w.c.date.label('last_workout_date')]) \ .select_from(e.outerjoin(w)) \ .where(e.c.user_id == user_id) \ .where(e.c.is_deleted == false()) \ .order_by(nullslast(desc(w.c.date))) return await db.fetch_all(query)
def list_schemas(): """Return the page which will display the list of schemas.""" # Order by schema wich validates the most JSON objects. hot_schemas = (db.session.query( JsonObject.schema_id, func.count("*").label("JsonObject_count")).group_by( JsonObject.schema_id).subquery()) schemas = (db.session.query(Schema).outerjoin( hot_schemas, (Schema.id == hot_schemas.c.schema_id)).order_by( nullslast(desc(hot_schemas.c.JsonObject_count)))) return render_template("schemas.html", schemas=schemas)
def list_organizations(): """Return the page which will display the list of organizations.""" # Order by organization wich provides the most JSON objects. big_contributors = (db.session.query( JsonObject.org_id, func.count("*").label("JsonObject_count")).group_by( JsonObject.org_id).subquery()) organizations = (db.session.query(Organization).outerjoin( big_contributors, (Organization.id == big_contributors.c.org_id)).order_by( nullslast(desc(big_contributors.c.JsonObject_count)))) return render_template("organizations.html", organizations=organizations)
def list_organizations(): """Return the page which will display the list of organizations.""" # Order by organization wich provides the most JSON objects. is_membership_restricted = int( request.args.get("is_membership_restricted", 1)) == 1 big_contributors = (db.session.query( JsonObject.org_id, func.count("*").label("JsonObject_count")).group_by( JsonObject.org_id).subquery()) if not is_membership_restricted: organizations = ( db.session.query(Organization).filter( Organization.is_membership_restricted == False) # noqa .outerjoin( big_contributors, (Organization.id == big_contributors.c.org_id)).order_by( nullslast(desc(big_contributors.c.JsonObject_count)))) else: organizations = (db.session.query(Organization).outerjoin( big_contributors, (Organization.id == big_contributors.c.org_id)).order_by( nullslast(desc(big_contributors.c.JsonObject_count)))) return render_template("organizations.html", organizations=organizations)
def get_release_groups_for_label(label_mbid, release_types=None, limit=None, offset=None): """Get all release groups linked to a label. Args: label_id (uuid): MBID of the label. release_types (list): List of types of release groups to be fetched. The supported release_types are 'album', 'single', 'ep', 'broadcast', and 'other'. limit (int): Max number of release groups to return. offset (int): Offset that can be used in conjunction with the limit. Returns: Tuple containing the list of dictionaries of release groups and the total count of the release groups. The list of dictionaries of release groups is ordered by release year, release month, release date, and release name. In case one of these is set to NULL, it will be ordered last. List also contains release groups with null type if 'Other' is in the list of release types. """ label_mbid = str(label_mbid) includes_data = defaultdict(dict) if release_types is None: release_types = [] release_types = get_mapped_release_types(release_types) include_null_type = True if "Other" in release_types else False with mb_session() as db: release_groups_query = _get_release_groups_for_label_query(db, label_mbid, release_types, include_null_type) count = release_groups_query.count() release_groups = release_groups_query.order_by( nullslast(models.ReleaseGroupMeta.first_release_date_year.desc()), nullslast(models.ReleaseGroupMeta.first_release_date_month.desc()), nullslast(models.ReleaseGroupMeta.first_release_date_day.desc()), nullslast(models.ReleaseGroup.name.asc()) ).limit(limit).offset(offset).all() for release_group in release_groups: includes_data[release_group.id]['meta'] = release_group.meta release_groups = [serialize_release_groups(release_group, includes_data[release_group.id]) for release_group in release_groups] return release_groups, count
def project_dataframe( cls, calc_df: Dataframe, return_taxons: Dict[TaxonExpressionStr, Taxon], physical_data_sources: Set[str], order_by: Optional[List[TaxonDataOrder]] = None, limit: Optional[int] = None, offset: Optional[int] = None, ) -> Dataframe: """ Applies in this order: - filtering - ordering - limiting and offsetting """ for order_by_rule in order_by or []: if order_by_rule.taxon not in return_taxons: raise InvalidRequest( 'request.order_by', f'Taxon "{order_by_rule.taxon}" used in order_by clause must be also selected.' ) projected_sql_and_df_columns, final_query = cls._project_columns( calc_df.query, calc_df, return_taxons) final_query = final_query.select_from(calc_df.query) projected_df_columns = Dataframe.dataframe_columns_to_map( [df_col for _, df_col in projected_sql_and_df_columns]) if order_by: final_query = final_query.order_by(*[ nullslast(ORDER_BY_FUNCTIONS[item.type](column( safe_identifier(item.taxon)))) for item in (order_by or []) ]) if limit is not None: final_query = final_query.limit(limit) if offset is not None: final_query = final_query.offset(offset) return Dataframe( final_query, projected_df_columns, calc_df.used_model_names, used_physical_data_sources=physical_data_sources, )
def _order_by(self, query, joins, sort_joins, sort_field, sort_desc): """ Apply order_by to the query :param query: Query :pram joins: Current joins :param sort_joins: Sort joins (properties or tables) :param sort_field: Sort field :param sort_desc: Select sort order: * True: for descending order * False or None: for ascending default order * 'LAST': for NULLS LAST * 'FIRST': for NULLS FIRST """ if sort_field is not None: # Handle joins query, joins, alias = self._apply_path_joins(query, joins, sort_joins, inner_join=False) column = sort_field if alias is None else getattr( alias, sort_field.key) if sort_desc is True: if isinstance(column, tuple): query = query.order_by(*map(desc, column)) else: query = query.order_by(desc(column)) elif sort_desc is False: if isinstance(column, tuple): query = query.order_by(*column) else: query = query.order_by(column) elif sort_desc == 'LAST': query = query.order_by(nullslast(desc(column))) elif sort_desc == 'FIRST': query = query.order_by(nullsfirst(desc(column))) return query, joins
def get(self): """Fetches all (interactive) pipeline runs. These pipeline runs are either pending, running or have already completed. Runs are ordered by started time descending. """ query = models.InteractivePipelineRun.query # Ability to query a specific runs given the `pipeline_uuid` or # `project_uuid` through the URL (using `request.args`). if "pipeline_uuid" in request.args and "project_uuid" in request.args: query = query.filter_by( pipeline_uuid=request.args.get("pipeline_uuid") ).filter_by(project_uuid=request.args.get("project_uuid")) elif "project_uuid" in request.args: query = query.filter_by(project_uuid=request.args.get("project_uuid")) runs = query.order_by(nullslast(models.PipelineRun.started_time.desc())).all() return {"runs": [run.__dict__ for run in runs]}, 200
def index(): sorter = request.args.get("sorter", None) if sorter is None: sorter = "random" initial_sorting = True else: initial_sorting = False order = request.args.get("order", None) criterion = SORTER.get(sorter, DEFAULT_SORTER) if order == DEFAULT_ORDER: criterion = desc(criterion) projects = Project.query.filter(Project.is_active.is_(True)).order_by( nullslast(criterion)) return { "projects": projects, "sorter": sorter, "initial_sorting": initial_sorting, "order": order, "DEFAULT_ORDER": DEFAULT_ORDER, }
def get_release_groups_for_artist(artist_id, release_types=None, limit=None, offset=None): """Get all release groups linked to an artist. Args: artist_id (uuid): MBID of the artist. release_types (list): List of types of release groups to be fetched. limit (int): Max number of release groups to return. offset (int): Offset that can be used in conjunction with the limit. Returns: Tuple containing the list of dictionaries of release groups ordered by release year and the total count of the release groups. """ artist_id = str(artist_id) includes_data = defaultdict(dict) if release_types is None: release_types = [] release_types = [release_type.lower() for release_type in release_types] # map release types to their case sensitive name in musicbrainz.release_group_primary_type table in the database release_types_mapping = { 'album': 'Album', 'single': 'Single', 'ep': 'EP', 'broadcast': 'Broadcast', 'other': 'Other' } release_types = [release_types_mapping[release_type] for release_type in release_types] with mb_session() as db: release_groups_query = _get_release_groups_for_artist_query(db, artist_id, release_types) count = release_groups_query.count() release_groups = release_groups_query.order_by( nullslast(models.ReleaseGroupMeta.first_release_date_year.desc()) ).limit(limit).offset(offset).all() for release_group in release_groups: includes_data[release_group.id]['meta'] = release_group.meta release_groups = ([serialize_release_groups(release_group, includes_data[release_group.id]) for release_group in release_groups], count) return release_groups
def list_reviewer(language_id, sort_key, order): """List of reviewers applying filters and sorting""" reviewers = ( Reviewer.query.outerjoin( ReviewRequest, Reviewer.id == ReviewRequest.reviewer_id ) .add_columns( Reviewer.id.label("id"), Reviewer.first_name.label("first_name"), Reviewer.last_name.label("last_name"), func.count(ReviewRequest.id).label("review_count"), func.max(ReviewRequest.review_date).label("last_review"), ) .group_by(Reviewer.id, Reviewer.first_name, Reviewer.last_name) ) if language_id: reviewers = reviewers.filter( Reviewer.languages.any(ReviewLanguage.id == language_id) ) sorting = { "asc": lambda x: nullsfirst(asc(x)), "desc": lambda x: nullslast(desc(x)), }[order] return reviewers.order_by(sorting(sort_key)).all()
def _build_query_window_aggregations( self, taxon_to_model: Dict[TaxonSlugExpression, HuskyModel], ordered_query_joins: Sequence[QueryJoins], ) -> Select: """ Generates query for taxons which need window functions for aggregation :param taxon_to_model: Map of taxon slugs (key) and models they are coming from (value) :param ordered_query_joins: List of joins """ selectors = [] # generate inner query with window aggregation functions for taxon_slug_expression, taxon in sorted( self.projection_taxons.items(), key=lambda x: str(x[0])): model = taxon_to_model[taxon_slug_expression] if (taxon.tel_metadata and taxon.tel_metadata.aggregation_definition and taxon.tel_metadata.aggregation_definition.params and taxon.tel_metadata_aggregation_type in self._AGGREGATION_WINDOW_FUNCTIONS): # find the order_by columns order_by = [] window_params = cast( AggregationParamsSortDimension, taxon.tel_metadata.aggregation_definition.params) for field in window_params.sort_dimensions: col = taxon_to_model[TaxonSlugExpression( field.taxon)].taxon_sql_accessor( self.ctx, field.taxon) order_by_dir = field.order_by or TaxonOrderType.asc order_by.append( nullslast(ORDER_BY_FUNCTIONS[order_by_dir]( literal_column(col)))) # apply window aggregation functions column = self._AGGREGATION_WINDOW_FUNCTIONS[ taxon.tel_metadata_aggregation_type](literal_column( model.taxon_sql_accessor(self.ctx, taxon.slug))).over( partition_by=self.get_partition_by_columns(model), order_by=order_by) else: # otherwise, render the columns "as-is" column = literal_column( model.taxon_sql_accessor(self.ctx, taxon.slug)) selectors.append(column.label(taxon.slug_safe_sql_identifier)) # add joins to the inner query inner_query = select(selectors).select_from( self._build_from_joins(ordered_query_joins)) # apply scope filters to the inner query inner_query = ScopeGuard.add_scope_row_filters( self.ctx, self.scope, inner_query, self.taxon_model_info_map) # update taxon model info map, because we're selecting from outer query and not the inner query self._rebuild_taxon_info_map_inner_query() # then, we use prepare the outer query on which we can safely apply GROUP BY return self._build_selectors(lambda _, taxon_slug: safe_identifier( taxon_slug)).select_from(inner_query)
def standings(): res = Result.query.order_by(nullslast(Result.tot_score.desc()), Result.tot_time).all() return render_template('standings.html', results=res)
def get_workflows(paginate=None): # noqa r"""Get all workflows. --- get: summary: Returns all workflows. description: >- This resource is expecting a user UUID. The information related to all workflows for a given user will be served as JSON operationId: get_workflows produces: - application/json parameters: - name: user in: query description: Required. UUID of workflow owner. required: true type: string - name: type in: query description: Required. Type of workflows. required: true type: string - name: verbose in: query description: Optional flag to show more information. required: false type: boolean - name: search in: query description: Filter workflows by name. required: false type: string - name: sort in: query description: Sort workflows by creation date (asc, desc). required: false type: string - name: status in: query description: Filter workflows by list of statuses. required: false type: array items: type: string - name: page in: query description: Results page number (pagination). required: false type: integer - name: size in: query description: Number of results per page (pagination). required: false type: integer - name: include_progress in: query description: Include progress information of the workflows. required: false type: boolean - name: include_workspace_size in: query description: Include size information of the workspace. required: false type: boolean - name: include_retention_rules in: query description: Include workspace retention rules of the workflows. type: boolean - name: workflow_id_or_name in: query description: Optional analysis UUID or name to filter. required: false type: string responses: 200: description: >- Requests succeeded. The response contains the current workflows for a given user. schema: type: object properties: total: type: integer items: type: array items: type: object properties: id: type: string name: type: string status: type: string size: type: object properties: raw: type: number human_readable: type: string user: type: string created: type: string progress: type: object launcher_url: type: string x-nullable: true examples: application/json: [ { "id": "256b25f4-4cfb-4684-b7a8-73872ef455a1", "name": "mytest.1", "status": "running", "size":{ "raw": 10490000, "human_readable": "10 MB" }, "user": "******", "created": "2018-06-13T09:47:35.66097", "launcher_url": "https://github.com/reanahub/reana-demo-helloworld.git", }, { "id": "3c9b117c-d40a-49e3-a6de-5f89fcada5a3", "name": "mytest.2", "status": "finished", "size":{ "raw": 12580000, "human_readable": "12 MB" }, "user": "******", "created": "2018-06-13T09:47:35.66097", "launcher_url": "https://example.org/specs/reana-snakemake.yaml", }, { "id": "72e3ee4f-9cd3-4dc7-906c-24511d9f5ee3", "name": "mytest.3", "status": "created", "size":{ "raw": 184320, "human_readable": "180 KB" }, "user": "******", "created": "2018-06-13T09:47:35.66097", "launcher_url": "https://zenodo.org/record/1/reana.yaml", }, { "id": "c4c0a1a6-beef-46c7-be04-bf4b3beca5a1", "name": "mytest.4", "status": "created", "size": { "raw": 1074000000, "human_readable": "1 GB" }, "user": "******", "created": "2018-06-13T09:47:35.66097", "launcher_url": null, } ] 400: description: >- Request failed. The incoming data specification seems malformed. 404: description: >- Request failed. User does not exist. examples: application/json: { "message": "User 00000000-0000-0000-0000-000000000000 does not exist" } 500: description: >- Request failed. Internal controller error. examples: application/json: { "message": "Internal workflow controller error." } """ try: user_uuid = request.args["user"] user = User.query.filter(User.id_ == user_uuid).first() type_ = request.args.get("type", "batch") verbose = json.loads(request.args.get("verbose", "false").lower()) sort = request.args.get("sort", "desc") search = request.args.get("search", "") status_list = request.args.get("status", "") include_progress = request.args.get("include_progress", verbose) include_workspace_size = request.args.get("include_workspace_size", verbose) include_retention_rules = request.args.get("include_retention_rules", verbose) workflow_id_or_name = request.args.get("workflow_id_or_name") if not user: return jsonify({"message": "User {} does not exist".format(user_uuid)}), 404 workflows = [] query = user.workflows if search: search = json.loads(search) search_val = search.get("name")[0] query = query.filter(Workflow.name.ilike("%{}%".format(search_val))) if status_list: workflow_status = [RunStatus[status] for status in status_list.split(",")] query = query.filter(Workflow.status.in_(workflow_status)) if workflow_id_or_name: query = ( query.filter(Workflow.id_ == workflow_id_or_name) if is_uuid_v4(workflow_id_or_name) else query.filter(Workflow.name == workflow_id_or_name) ) column_sorted = Workflow.created.desc() if sort in ["disk-desc", "cpu-desc"]: resource_type = sort.split("-")[0] resource = get_default_quota_resource(resource_type) query = query.join( WorkflowResource, and_( Workflow.id_ == WorkflowResource.workflow_id, WorkflowResource.resource_id == resource.id_, ), isouter=True, ) column_sorted = nullslast(WorkflowResource.quota_used.desc()) elif sort in ["asc", "desc"]: column_sorted = getattr(Workflow.created, sort)() pagination_dict = paginate(query.order_by(column_sorted)) for workflow in pagination_dict["items"]: workflow_response = { "id": workflow.id_, "name": get_workflow_name(workflow), "status": workflow.status.name, "user": user_uuid, "launcher_url": workflow.launcher_url, "created": workflow.created.strftime(WORKFLOW_TIME_FORMAT), "progress": get_workflow_progress( workflow, include_progress=include_progress ), } if type_ == "interactive" or verbose: int_session = workflow.sessions.first() if int_session: workflow_response["session_type"] = int_session.type_.name workflow_response["session_uri"] = int_session.path workflow_response["session_status"] = int_session.status.name # Skip workflow if type is interactive and there is no session elif type_ == "interactive": continue empty_disk_usage = { "human_readable": "", "raw": -1, } if include_retention_rules: rules = workflow.retention_rules.all() workflow_response["retention_rules"] = [ rule.serialize() for rule in rules ] if include_workspace_size: workflow_response["size"] = ( workflow.get_quota_usage() .get("disk", {}) .get("usage", empty_disk_usage) ) else: workflow_response["size"] = empty_disk_usage workflows.append(workflow_response) pagination_dict["items"] = workflows pagination_dict["user_has_workflows"] = user.workflows.first() is not None return jsonify(pagination_dict), 200 except (ValueError, KeyError): return jsonify({"message": "Malformed request."}), 400 except json.JSONDecodeError: return jsonify({"message": "Your request contains not valid JSON."}), 400 except Exception as e: return jsonify({"message": str(e)}), 500
def list( session, project_id=None, org=None, limit=100, return_kind="objects", archived=False, date_to=None, # datetime date_from=None, # datetime date_to_string: str = None, date_from_string: str = None, name: str = None, name_match_type: str = "ilike", # substring and helps if case Aa is off order_by_class_and_attribute=None, order_by_direction=desc, public_only=False): """ """ query = session.query(UserScript) # Assume we must either have public script or project id if public_only is True: query = query.filter(UserScript.is_public == True) else: query = query.filter(UserScript.project_id == project_id) if name: if name_match_type == "ilike": name_search = "%{}%".format(name) query = query.filter(UserScript.name.ilike(name_search)) else: query = query.filter(UserScript.name == name) if date_from or date_to: if date_from: query = query.filter(UserScript.created_time >= date_from) if date_to: query = query.filter(UserScript.created_time <= date_to) else: query = regular_methods.regular_query( query=query, date_from_string=date_from_string, date_to_string=date_to_string, base_class=UserScript, created_time_string='time_updated') if archived is False: query = query.filter(UserScript.archived == False) if order_by_class_and_attribute: query = query.order_by( nullslast(order_by_direction(order_by_class_and_attribute))) if return_kind == "count": return query.limit(limit).count() if return_kind == "objects": return query.limit(limit).all()
def sort(query, key, model, aliases=None, join_columns=None, clear=False, hide_null=False, index_column=None, nulls_last=False): """Sort query using string-formatted columns. :param query: Original query :param options: Sort column name; prepend with "-" for descending sort :param model: SQLAlchemy model :param join_columns: Mapping of column names to sort and join rules; used for sorting on related columns :param clear: Clear existing sort conditions :param hide_null: Exclude null values on sorted column(s) :param index_column: :param nulls_last: Sort null values on sorted column(s) last in results; Ignored if hide_null is True """ # Start off assuming we are dealing with a sort column, not a sort # expression. is_expression = False expression_field = None expression_type = None null_sort = None if clear: query = query.order_by(False) # If the query contains multiple entities (i.e., isn't a simple query on a # model), looking up the sort key on the model may lead to invalid queries. # In this case, use the string name of the sort key. sort_model = (model if len(query._entities) == 1 and hasattr(query._entities[0], 'mapper') else None) column, order, relationship = parse_option(key, model=sort_model, aliases=aliases, join_columns=join_columns, query=query) # Store the text representation (name) of the sorting column in case we # swap it for an expression instead. if hasattr(column, 'key'): column_name = column.key else: column_name = column if model: # Check to see if the model has a sort_expressions attribute on it, # which contains a dictionary of column mappings to SQL expressions. # If the model has this and there is a matching expression for the # column, use the expression instead. if hasattr( model, 'sort_expressions') and column_name in model.sort_expressions: column = model.sort_expressions[column_name]['expression'] expression_field = model.sort_expressions[column_name]['field'] expression_type = model.sort_expressions[column_name]['type'] null_sort = model.sort_expressions[column_name].get( 'null_sort', model.sort_expressions[column_name]['expression']) is_expression = True sort_column = order(column) if nulls_last and not hide_null: query = query.order_by(sa.nullslast(sort_column)) else: query = query.order_by(sort_column) if relationship: query = query.join(relationship) if hide_null: query = query.filter(column != None) # noqa return query, ( column, order, column_name, is_expression, expression_field, expression_type, null_sort, )
def _nullslast(obj): if current_app.config['SQLALCHEMY_DATABASE_URI'].startswith("sqlite"): return obj else: return nullslast(obj)
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)