def generate_query(model, path, filters=None):
"""
Generate a SELECT query to fetch `model` ids along the path with given
`filters` on the last model in `path`.
:param model: A :ref:`declarative <sqla:declarative_toplevel>` class.
:param path:
:param [sqlalchemy.sql.expression.BinaryExpression] filters:
:rtype: A :ref:`sqlalchemy.orm.query.Query` object
"""
query = Query(model.id)
if path:
# The below is a fix in case the same table is joined
# multiple times. In that case, we alias everything except
# the last path and then filter on the last path.
path_list = path.split(".")
last_path = path_list[-1]
path_list = path_list[:-1]
if path_list:
query = query.join(*path_list, aliased=True)
query = query.join(last_path, from_joinpoint=True)
if filters is not None:
if isinstance(filters, list):
query = query.filter(*filters)
else:
query = query.filter(filters)
return query
def generate_query(model, path, filters=None):
"""
Generate a SELECT query to fetch `model` ids along the path with given
`filters` on the last model in `path`.
:param model: A :ref:`declarative <sqla:declarative_toplevel>` class.
:param path:
:param [sqlalchemy.sql.expression.BinaryExpression] filters:
:rtype: A :ref:`sqlalchemy.orm.query.Query` object
"""
# We start with the query selecting the ids of the models we want to return.
query = Query(model.id)
if path:
# In case path is not blank, we need to alias the model id while joining
# to prevent referencing the same table again.
query = (Query(aliased(model).id))
# The below is a fix in case the same table is joined
# multiple times. In that case, we alias everything except
# the last path and then filter on the last path.
path_list = path.split(".")
last_path = path_list[-1]
path_list = path_list[:-1]
if path_list:
query = query.join(*path_list, aliased=True)
# The last path is purposfully left out from being aliased to make it easier
# to contrunct filter conditions.
query = query.join(last_path, from_joinpoint=True)
if filters is not None:
if isinstance(filters, list):
query = query.filter(*filters)
else:
query = query.filter(filters)
return query
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 _most_recent_state_ids_subquery(query: Query) -> Query:
"""Find the most recent state ids for all entiites."""
# We did not get an include-list of entities, query all states in the inner
# query, then filter out unwanted domains as well as applying the custom filter.
# This filtering can't be done in the inner query because the domain column is
# not indexed and we can't control what's in the custom filter.
most_recent_states_by_date = (
query.session.query(
States.entity_id.label("max_entity_id"),
func.max(States.last_updated).label("max_last_updated"),
)
.filter(
(States.last_updated >= bindparam("run_start"))
& (States.last_updated < bindparam("utc_point_in_time"))
)
.group_by(States.entity_id)
.subquery()
)
most_recent_state_ids = (
query.session.query(func.max(States.state_id).label("max_state_id"))
.join(
most_recent_states_by_date,
and_(
States.entity_id == most_recent_states_by_date.c.max_entity_id,
States.last_updated == most_recent_states_by_date.c.max_last_updated,
),
)
.group_by(States.entity_id)
.subquery()
)
return query.join(
most_recent_state_ids,
States.state_id == most_recent_state_ids.c.max_state_id,
)
def _apply_query_permissions(query: Query, user: UKRDCUser):
units = Permissions.unit_codes(user.permissions)
if Permissions.UNIT_WILDCARD in units:
return query
return (query.join(LinkRecord).join(Person).join(PidXRef).filter(
PidXRef.sending_facility.in_(units)))
def _lookup_fish_tug_strength_by_fish_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Fish, alchemyMapping.FishTugStrength.fish_angler_fish_id
== alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def _query_join_relation(
self,
query: Query,
root_relation: str,
aliases_mapping: Dict[str, AliasedClass] = None,
) -> Query:
"""
Helper function that applies necessary joins for dotted columns on a
SQLAlchemy query object
:param query: SQLAlchemy query object
:param root_relation: The root part of a dotted column, so the root relation
:return: Transformed SQLAlchemy Query
"""
if aliases_mapping is None:
aliases_mapping = {}
relations = self.get_related_model_and_join(root_relation)
for relation in relations:
model_relation, relation_join = relation
# Use alias if it's not a custom relation
if not hasattr(relation_join, "clauses"):
model_relation = aliased(model_relation, name=root_relation)
aliases_mapping[root_relation] = model_relation
relation_pk = self.get_pk(model_relation)
if relation_join.left.foreign_keys:
relation_join = BinaryExpression(relation_join.left,
relation_pk,
relation_join.operator)
else:
relation_join = BinaryExpression(relation_join.right,
relation_pk,
relation_join.operator)
query = query.join(model_relation, relation_join, isouter=True)
return query
def _lookup_bait_comments_by_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Bait, alchemyMapping.BaitComment.bait_angler_bait_id ==
alchemyMapping.Bait.bait_angler_bait_id).filter(
alchemyMapping.Bait.bait_xivapi_item_id ==
kwargs['bait_xivapi_item_id'])
def _lookup_fish_caught_count_by_fish_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Fish, alchemyMapping.FishCaughtCount.fish_angler_fish_id
== alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def querybuilder(
query: Query,
filters: Union[Dict, List[Dict]],
) -> Query:
"""build SQLAlchemy Query object from JSON filter definition
Args:
query: sqlalchemy.orm.query.Query
filters: filter definition
Returns:
sqlalchemy.orm.query.Query
"""
_criterion = _build_criterion(query)
if isinstance(filters, Dict):
filters = [filters]
queries = []
for _filter in filters:
_query = query.join() # dup
for attr, expr in _filter.items():
if (not isinstance(expr, dict)) and (not isinstance(expr, list)):
expr = {'==': expr}
if isinstance(expr, dict):
for op, value in expr.items():
_query = _query.filter(_criterion(attr, op, value))
elif isinstance(expr, list):
boolean_op = expr.pop(0).lower()
criteria = []
for e in expr:
for op, value in e.items():
criteria.append(_criterion(attr, op, value))
if boolean_op == 'or':
_query = _query.filter(or_(*criteria))
elif boolean_op == 'and':
_query = _query.filter(and_(*criteria))
else:
raise ValueError('invalid boolean op: %s' % boolean_op)
else:
raise ValueError('invalid expr: %s' % expr)
queries.append(_query)
if len(queries) == 1:
return queries[0]
else:
_query = queries.pop(0)
for q in queries:
_query = _query.union(q)
return _query
def _lookup_spot_effective_bait_by_bait_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Bait,
alchemyMapping.SpotEffectiveBait.bait_angler_bait_id ==
alchemyMapping.Bait.bait_angler_bait_id).filter(
alchemyMapping.Bait.bait_xivapi_item_id ==
kwargs['bait_xivapi_item_id'])
def _lookup_spot_bait_total_fish_caught_by_bait_item_id(
req: Request, resp: Response, query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Bait,
alchemyMapping.SpotBaitTotalFishCaught.bait_angler_bait_id ==
alchemyMapping.Bait.bait_angler_bait_id).filter(
alchemyMapping.Bait.bait_xivapi_item_id ==
kwargs['bait_xivapi_item_id'])
def _lookup_spot_available_fish_by_fish_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Fish,
alchemyMapping.SpotAvailableFish.fish_angler_fish_id ==
alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def _lookup_fish_involved_leve_by_fish_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Fish,
alchemyMapping.FishInvolvedLeve.fish_angler_fish_id ==
alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def element_atomic_weight(self, zeq, reference=None):
z = self._get_z(zeq)
q = Query(ElementAtomicWeightProperty.value)
q = q.join(Element)
q = q.filter(Element.z == z)
exception = ValueError('Unknown atomic weight for z="{0}" and '
'reference="{1}"'.format(z, reference))
return self._query_with_references(q, exception, reference)
def element_mass_density_kg_per_m3(self, zeq, reference=None):
z = self._get_z(zeq)
q = Query(ElementMassDensityProperty.value_kg_per_m3)
q = q.join(Element)
q = q.filter(Element.z == z)
exception = ValueError('Unknown mass density for z="{0}" and '
'reference="{1}"'.format(z, reference))
return self._query_with_references(q, exception, reference)
def _lookup_bait_alt_currency_by_item_id(req: Request, resp: Response,
query: Query, *args, **kwargs):
return query.join(
alchemyMapping.Bait,
alchemyMapping.BaitAltCurrencyPrice.bait_angler_bait_id ==
alchemyMapping.Bait.bait_angler_bait_id).filter(
alchemyMapping.Bait.bait_xivapi_item_id ==
kwargs['bait_xivapi_item_id'])
def _lookup_fish_desynthesis_item_by_fish_item_id(req: Request, resp: Response,
query: Query, *args,
**kwargs):
return query.join(
alchemyMapping.Fish,
alchemyMapping.FishDesynthesisItem.fish_angler_fish_id ==
alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def _lookup_fish_bait_preference_by_fish_item_id(req: Request, resp: Response,
query: Query, *args,
**kwargs):
return query.join(
alchemyMapping.Fish,
alchemyMapping.FishBaitPreference.fish_angler_fish_id ==
alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def transform(query: Query):
"""Add a join condition with local_roles and add a filter by role_name in the query."""
cls = self.cls.local_roles.mapper.class_
query = query.join(cls).filter(
and_(
op(cls.principal_id, other),
cls.role_name == self.role_name
)
)
return query
def _lookup_spot_bait_fish_catch_info_by_fish_item_id(req: Request,
resp: Response,
query: Query, *args,
**kwargs):
return query.join(
alchemyMapping.Fish,
alchemyMapping.SpotBaitFishCatchInfo.fish_angler_fish_id ==
alchemyMapping.Fish.fish_angler_fish_id).filter(
alchemyMapping.Fish.fish_xivapi_item_id ==
kwargs['fish_xivapi_item_id'])
def element_name(self, zeq, language='en', reference=None):
z = self._get_z(zeq)
q = Query(ElementNameProperty.name)
q = q.filter(ElementNameProperty.language_code == language)
q = q.join(Element)
q = q.filter(Element.z == z)
exception = ValueError('Unknown name for z="{0}", '
'language="{1}" and '
'reference="{2}"'
.format(z, language, reference))
return self._query_with_references(q, exception, reference)
def _lookup_spot_comment_by_spot_gathering_type_unique_id(
req: Request, resp: Response, query: Query, *args, **kwargs):
# noinspection PyProtectedMember
if alchemyMapping.Spot not in [
mapper.entity for mapper in query._join_entities
]:
query = query.join(
alchemyMapping.Spot, alchemyMapping.SpotComment.spot_angler_spot_id
== alchemyMapping.Spot.spot_angler_spot_id)
return query.filter(alchemyMapping.Spot.spot_gathering_type_unique_id ==
kwargs['spot_gathering_type_unique_id'])
def _lookup_spot_bait_total_fish_caught_by_spot_gathering_type(
req: Request, resp: Response, query: Query, *args, **kwargs):
# noinspection PyProtectedMember
if alchemyMapping.Spot not in [
mapper.entity for mapper in query._join_entities
]:
query = query.join(
alchemyMapping.Spot,
alchemyMapping.SpotBaitTotalFishCaught.spot_angler_spot_id ==
alchemyMapping.Spot.spot_angler_spot_id)
return query.filter(alchemyMapping.Spot.spot_gathering_type ==
kwargs['spot_gathering_type'])
def _most_recent_state_ids_entities_subquery(query: Query) -> Query:
"""Query to find the most recent state id for specific entities."""
# We got an include-list of entities, accelerate the query by filtering already
# in the inner query.
most_recent_state_ids = (
query.session.query(func.max(States.state_id).label("max_state_id"))
.filter(
(States.last_updated >= bindparam("run_start"))
& (States.last_updated < bindparam("utc_point_in_time"))
)
.filter(States.entity_id.in_(bindparam("entity_ids", expanding=True)))
.group_by(States.entity_id)
.subquery()
)
return query.join(
most_recent_state_ids,
States.state_id == most_recent_state_ids.c.max_state_id,
)
def get_column_and_joins( name, context4root, must_alias4root ={} ):
'''name = multilevel a.b.c.d.e
returns (clause-value, join_clause, is_plain)
e.g. (rootvalue, None, True) #1 level: x
(lastcolumn, None, True) #2 level: x.y
(lastcolumn, join_clause, False) #>2 level x.y.z
'''
path = name.split('.')
root_name = path.pop(0)
root_value = context4root[ root_name]
if not path:
# if isinstance( root_value, klas ot mapnatite):
# return root_value.db_id
return root_value, None, True
#eventualy return a bindparam( name=name, type=??...), store the expr and
#and then use select(expr, params={name=value})
#see way -f in explanation at bottom
# if root_value ne e klas ot mapnatite:
if 0:
attr_name = path[-1]
via_names = path[:-1]
from sqlalchemy.orm.query import Query
q = Query( root_value)
q = q.join( via_names, aliased= must_alias4root.get( root_name,None) )
print 'AAA', q._joinpoint, q._criterion, q._from_obj
#needs the query's mapper; for now assume primary
mapper0 = orm.class_mapper( root_value)
prop, clause, mapper, lasttable = join_via( path, mapper0, must_alias= must_alias4root.get( root_name,None) )
if _debug: print 'cols/joins:', mapper, prop, 'lasttable:', lasttable, 'clause:', clause
#hope for the first if many...
if isinstance( prop, orm.properties.ColumnProperty):
lastcol = prop.columns[0]
elif isinstance( prop, orm.properties.PropertyLoader):
for c in foreign_keys( prop):
lastcol = c
break
else:
raise NotImplementedError, prop
if lasttable: #self-ref #now always because of must_aliases
if _debug: print '>>>>?', lastcol, getattr( lasttable,'original',lasttable)#.__class__
if 0:
lastcol = lasttable.corresponding_column( lastcol)
else: #see PropertyLoader._create_polymorphic_joins in orm/properies.py
self_colequivalents = equivs( mapper)
for col in [lastcol] + list( self_colequivalents.get( lastcol, [])):
#print col, lasttable.__class__
lc = corresponding_column( lasttable, col)
#print '*******', col, lc
if lc:
lastcol = lc
break
else:
assert 0, '%(name)s: Cannot find corresp.column for %(c)s in mapper:%(mapper)s, selectable %(lasttable)s' % locals()
if _debug: print '>>>>>', lastcol
if 0: print 'BBB', lastcol, clause
return lastcol, clause, False
