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