def overview(): q = Query(CsdcContestant) sc = onetimescorecard().subquery() q = q.outerjoin(sc, CsdcContestant.player_id == sc.c.player_id) totalcols = [] wktotal = [] wkbonuses = [] for col in ("fifteenrune", "sub40k", "zig", "lowxlzot", "nolairwin", "asceticrune"): totalcols.append(func.ifnull(getattr(sc.c, col), 0)) q = q.add_column(getattr(sc.c, col).label(col)) for wk in weeks: a = wk.sortedscorecard().subquery() totalcols.append(func.ifnull(a.c.total, 0)) wktotal.append(a.c.total) wkbonuses.append(func.ifnull(a.c.bonusone, 0) + func.ifnull(a.c.bonustwo, 0)) q = q.outerjoin(a, CsdcContestant.player_id == a.c.player_id ).add_column( a.c.total.label("wk" + wk.number)) return q.add_columns( sc.c.account_id.label("account_id"), sum(totalcols).label("grandtotal"), sum(wkbonuses).label("tiebreak"), sc.c.hiscore.label("hiscore"), (func.coalesce(*wktotal) != None).label("played") ).order_by(desc("grandtotal"),desc("tiebreak"),desc("hiscore"),desc("played"))
def overview(): q = Query(Player) totalcols = [] for wk in weeks: wk_n = "wk" + wk.number a = wk.scorecard().subquery() q = q.outerjoin(a, Player.id == a.c.player_id).add_column( a.c.subtotal.label(wk_n) ).add_column(a.c.time.label(wk_n + "time")).add_column( a.c.turns.label(wk_n + "turns")).add_column( a.c.slimy.label(wk_n + "slimy")).add_column( a.c.silver.label(wk_n + "silver")).add_column( a.c.iron.label(wk_n + "iron")).add_column( a.c.bone.label(wk_n + "bone")).add_column( a.c.obsidian.label(wk_n + "obsidian") ).add_column( a.c.icy.label(wk_n + "icy")).add_column( a.c.pan.label(wk_n + "pan")).add_column( a.c.qaz.label(wk_n + "qaz") ).add_column(a.c.chei.label( wk_n + "chei")).add_column( a.c.lucy.label(wk_n + "lucy") ).add_column( a.c.jiyva.label(wk_n + "jiyva")) return q
def base_query(self, query: Query): query = query.join(ValueSet).options( joinedload(Value.valueset).joinedload( ValueSet.references).joinedload(ValueSetReference.source)) if self.language: query = query.join(ValueSet.parameter) if self.language.level == models.LanguoidLevel.group: children = self.language.children children_pks = [child.pk for child in children] filtered = query.filter(ValueSet.language_pk.in_(children_pks)) filtered = filtered.join(ValueSet.language) return filtered return query.filter(ValueSet.language_pk == self.language.pk) if self.parameter: query = query.join(ValueSet.language) query = query.outerjoin(DomainElement).options( joinedload(Value.domainelement)) return query.filter(ValueSet.parameter_pk == self.parameter.pk) if self.contribution: query = query.join(ValueSet.parameter) return query.filter( ValueSet.contribution_pk == self.contribution.pk) query = query.join(ValueSet.language).join(ValueSet.parameter) return query
def overview(): q = Query(CsdcContestant) totalcols = [] for wk in weeks: a = wk.scorecard().subquery() totalcols.append(func.ifnull(a.c.total, 0)) q = q.outerjoin(a, CsdcContestant.player_id == a.c.player_id).add_column( a.c.total.label("wk" + wk.number)) return q.add_column(sum(totalcols).label("grandtotal")).order_by( desc("grandtotal"))
def _apply_events_types_and_states_filter(hass: HomeAssistant, query: Query, old_state: States) -> Query: events_query = (query.outerjoin( States, (Events.event_id == States.event_id)).outerjoin( old_state, (States.old_state_id == old_state.state_id )).filter((Events.event_type != EVENT_STATE_CHANGED) | _missing_state_matcher(old_state)).filter( (Events.event_type != EVENT_STATE_CHANGED) | _not_continuous_entity_matcher())) return _apply_event_types_filter( hass, events_query, ALL_EVENT_TYPES).outerjoin( StateAttributes, (States.attributes_id == StateAttributes.attributes_id))
def filter_query(request: Request, query: Query, Model: RestalchemyBase, filter_by: str, value: str) -> Query: negate = filter_by.endswith("!") # negate filter when last char is ! less_equal = filter_by.endswith("<") # use less equal when last char is < greater_equal = filter_by.endswith( ">") # use greater equal when last char is > filter_by = filter_by.rstrip("!<>") # When a filter ends with an underscore, simply ignore it. # Appending '_' is allowed to avoid a conflict with a reserved word like # 'limit' or 'offset' etc. if filter_by.endswith("_"): filter_by = filter_by[:-1] # FilterModel is the model who's attribute will be used for the filter FilterModel: RestalchemyBase = Model if "." in filter_by: # when filtered by a full name assume a join model2_name, filter_by = filter_by.split(".", 1) Model2 = request.restalchemy_get_model(model2_name) if not Model2: raise AttributeNotFound(model2_name) if Model != Model2: try: FilterModel = Model2 = aliased(Model2) # type: ignore # LEFT JOIN so you can query `Model2.attr='null'` query = query.outerjoin(Model2) # FIXME: specify join argument like # ``query = query.outerjoin(Model2, Model.model2_name)`` # otherwise sqla can't find the join with some multiple filters like: # `/v3/creatives?bans.reason=null&advertiser.network_id!=9&sort=quickstats.today.clicks.desc` # Allow 1 more join to filter stuff like /campaigns?profile.segments_filter.segments_id=14 if "." in filter_by: model3_name, filter_by = filter_by.split(".", 1) Model3 = request.restalchemy_get_model(model3_name) if not Model3: raise AttributeNotFound(model3_name) # Join Model 3 with Model2 (SQLAlchemy knows how) # and filter attribute on Model3 if Model3 not in [Model, Model2]: FilterModel = aliased(Model3) # type: ignore query = query.outerjoin(FilterModel) except InvalidRequestError: raise AttributeWrong(model2_name) # FIXME: ???? validate list (otherwise DBAPIError is raised)! try: filter_attr = getattr(FilterModel, filter_by) except AttributeError: raise AttributeNotFound(filter_by) # If filter_attr is n to m relationship, the value is always `IN` and # we have to join the secondary table. if isinstance(filter_attr, InstrumentedAttribute) and hasattr( filter_attr.property, "secondary"): target: Any = request.restalchey_get_model( filter_attr.property.target.name) # Without `DISTINCT` models matching multiple filter values will return multiple # rows which sqlalchemy combines to one, resulting in less rows total then # the specified `limit` rows query = query.outerjoin(filter_attr) if isinstance(value, str) and value.lower() == "null": if negate: return query.filter(target.id != None) else: return query.filter(target.id == None) elif negate: return query.filter(target.id.notin_(value.split(","))).distinct() else: return query.filter(target.id.in_(value.split(","))).distinct() # else if isinstance(value, str) and "," in value: if less_equal or greater_equal: raise FilterInvalid( msg="Less or greater equal only allowed with single values.") if negate: return query.filter(filter_attr.notin_(value.split(","))) return query.filter(filter_attr.in_(value.split(","))) if isinstance(value, str) and "*" in value: if less_equal or greater_equal: raise FilterInvalid( msg="Less or greater equal is not allowed for wildcards (`*`)." ) validate(value, filter_attr) value = value.replace("*", "%") if negate: return query.filter(~getattr(FilterModel, filter_by).like(value)) else: return query.filter(getattr(FilterModel, filter_by).like(value)) if isinstance(value, str) and value.lower() == "null": value = None # type: ignore validate(value, filter_attr) if negate: return query.filter(filter_attr != value) if less_equal: return query.filter(filter_attr <= value) if greater_equal: return query.filter(filter_attr >= value) return query.filter(filter_attr == value)