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)
