def filter_dates( q: sqla.sql.Select, start: datetime.datetime = None, finish: datetime.datetime = None, ) -> sqla.sql.Select: if start: q = q.where(orm_items.Item.Published >= start, ) if finish: q = q.where(orm_items.Item.Published < finish, ) return q
def security(q: Query, stmt: sa.sql.Select) -> sa.sql.Select: """ Security: make sure that the user can only access their own data """ ALLOWED_USER_ID = 1 path = q.load_path if path == (Article, ): return stmt.where(q.Model.user_id == ALLOWED_USER_ID) elif path == (Article, 'author', User): return stmt.where(q.Model.id == ALLOWED_USER_ID) elif path == (Article, 'comments', Comment): return stmt.where(q.Model.user_id == ALLOWED_USER_ID) else: raise NotImplementedError
def apply_to_statement(self, query: QueryObject, target_Model: SAModelOrAlias, stmt: sa.sql.Select) -> sa.sql.Select: # Prepare the filter expression cursor = self.cursor_value if cursor is None: filter_expression = True limit = self.limit else: # Make sure the columns are still the same if set(cursor.cols) != query.sort.names: raise exc.QueryObjectError('You cannot adjust "sort" fields while using cursor-based pagination.') # Filter op = {'>': operator.gt, '<': operator.lt}[cursor.op] filter_expression = op( sa.tuple_(*( resolve_column_by_name(field.name, target_Model, where='skip') for field in query.sort.fields )), cursor.val ) limit = cursor.limit if limit is None: return stmt # Paginate # We will always load one more row to check if there's a next page if SA_14: return stmt.filter(filter_expression).limit(limit + 1) else: return stmt.where(filter_expression).limit(limit + 1)
def filter_languages( q: sqla.sql.Select, langs: typing.List[schema_feeds.Language], ) -> sqla.sql.Select: langs_name = [e.name for e in langs] q = q.where(orm_feeds.Feed.Language.in_(langs_name)) return q
def filter_like( q: sqla.sql.Select, score: schema_items.Like, user: orm_users.User, ) -> sqla.sql.Select: item_likes = orm_items.Item.likes.and_( orm_items.Like.UserID == user.UserID, ) q = q.join(item_likes) q = q.where(orm_items.Like.Score == score.name) return q
def apply_to_statement(self, stmt: sa.sql.Select) -> sa.sql.Select: """ Modify the Select statement: add the WHERE clause """ # Compile the conditions conditions = (self._compile_condition(condition) for condition in self.query.filter.conditions) # Add the WHERE clause if SA_13: stmt = stmt.where(sa.and_(*conditions)) else: stmt = stmt.filter(*conditions) # Done return stmt
def filter_magic( q: sqla.sql.Select, user: orm_users.User, unscored: bool = True, ) -> sqla.sql.Select: item_magic = orm_items.Item.magic.and_( orm_items.Magic.UserID == user.UserID, ) q = q.join(item_magic, isouter=True) q_where = orm_items.Item.magic.any(orm_items.Magic.UserID == user.UserID) if unscored: q_where = ~q_where q = q.where(q_where) return q
def filter_tags( q: sqla.sql.Select, tags: typing.List[orm_feeds.Tag], user: orm_users.User, ) -> sqla.sql.Select: feed_tags = orm_feeds.Feed.tags.and_( orm_feeds.Tag.UserID == user.UserID, ) q = q.join(feed_tags) q = q.where( orm_feeds.Tag.TagID.in_( [tag_it.TagID for tag_it in tags] ) ) return q
def prepare_query(self, q: sa.sql.Select) -> sa.sql.Select: """ Prepare the statement for loading: add columns to select, add filter condition Args: q: SELECT statement prepared by QueryExecutor.statement(). It has no columns yet, but has a select_from(self.target_model), unaliased. NOTE: we never alias the target model: the one we're loading. It would've made things too complicated. """ # Use SelectInLoader # self.query_info: primary key columns, the IN expression, etc # self._parent_alias: used with JOINed relationships where our table has to be joined to an alias of the parent table query_info = self.loader._query_info parent_alias = self.loader._parent_alias if query_info.load_with_join else NotImplemented effective_entity = self.target_model # [ADDED] Adapt pk_cols # [o] pk_cols = query_info.pk_cols # [o] in_expr = query_info.in_expr pk_cols = query_info.pk_cols in_expr = query_info.in_expr # [o] if not query_info.load_with_join: if not query_info.load_with_join: # [o] if effective_entity.is_aliased_class: # [o] pk_cols = [ effective_entity._adapt_element(col) for col in pk_cols ] # [o] in_expr = effective_entity._adapt_element(in_expr) adapter = SimpleColumnsAdapter(self.target_model) pk_cols = adapter.replace_many(pk_cols) in_expr = adapter.replace(in_expr) # [o] bundle_ent = orm_util.Bundle("pk", *pk_cols) # [o] entity_sql = effective_entity.__clause_element__() # [o] q = Select._create_raw_select( # [o] _raw_columns=[bundle_sql, entity_sql], # [o] _label_style=LABEL_STYLE_TABLENAME_PLUS_COL, # [CUSTOMIZED] if not query_info.load_with_join: q = add_columns(q, pk_cols) # [CUSTOMIZED] else: # NOTE: we cannot always add our FK columns: when `load_with_join` is used, these columns # may actually refer to columns from a M2M table with conflicting names! # Example: # SELECT articles.id, tags.id # FROM articles JOIN ... JOIN tags # So we have to rename them. We use "table.column" aliases because this horrible "." makes it clear # it's not just another column # label_prefix = self.source_model.__table__.name + '.' self.fk_label_prefix = self.source_model.__tablename__ + '.' # type: ignore[union-attr] q = add_columns(q, [ # [CUSTOMIZED] col.label(self.fk_label_prefix + col.key) for col in pk_cols ]) # Effective entity # This is the class that we select from # [o] if not query_info.load_with_join: # [o] q = q.select_from(effective_entity) # [o] else: # [o] q = q.select_from(self._parent_alias).join(...) # [CUSTOMIZED] if not query_info.load_with_join: q = q.select_from(self.target_model) else: if SA_13: q = q.select_from( sa.orm.join(parent_alias, self.target_model, onclause=getattr(parent_alias, self.key).of_type(self.target_model)) ) else: q = q.select_from(parent_alias).join( getattr(parent_alias, self.key).of_type(self.target_model) ) # [o] q = q.filter(in_expr.in_(sql.bindparam("primary_keys"))) if SA_13: q = q.where(in_expr.in_(sa.sql.bindparam("primary_keys", expanding=True))) else: q = q.filter(in_expr.in_(sa.sql.bindparam("primary_keys"))) return q
def _apply_window_over_foreign_key_pagination( self, stmt: sa.sql.Select, *, fk_columns: list[SAAttribute]) -> sa.sql.Select: """ Instead of the usual limit, use a window function over the given columns. This method is used with the selectin-load loading strategy to load a limited number of related items per every primary entity. Instead of using LIMIT, we will group rows over `fk_columns`, and impose a limit per group. This is achieved using a Window Function: SELECT *, row_number() OVER(PARTITION BY author_id) AS group_row_n FROM articles WHERE group_row_n < 10 This will result in the following table: id | author_id | group_row_n ------------------------------------ 1 1 1 2 1 2 3 2 1 4 2 2 5 2 3 6 3 1 7 3 2 """ skip, limit = self.skip, self.limit # Apply it only when there's a limit if not skip and not limit: return stmt # First, add a row counter adapter = SimpleColumnsAdapter(self.target_Model) row_counter_col = ( sa.func.row_number().over( # Groups are partitioned by self._window_over_columns, partition_by=adapter.replace_many( fk_columns), # type: ignore[arg-type] # We have to apply the same ordering from the outside query; # otherwise, the numbering will be undetermined order_by=adapter.replace_many( get_sort_fields_with_direction( self.query.sort, self.target_Model)) # type: ignore[arg-type] ) # give it a name that we can use later .label('__group_row_n')) stmt = add_columns(stmt, [row_counter_col]) # Wrap ourselves into a subquery. # This is necessary because Postgres does not let you reference SELECT aliases in the WHERE clause. # Reason: WHERE clause is executed before SELECT if SA_14: subquery = ( # Taken from: Query.from_self() stmt.correlate(None).subquery()._anonymous_fromclause( ) # type: ignore[attr-defined] ) else: subquery = (stmt.correlate(None).alias()) stmt = sa.select([ column for column in subquery.c if column.key != '__group_row_n' # skip this column. We don't need it. ]).select_from(subquery) # Apply the LIMIT condition using row numbers # These two statements simulate skip/limit using window functions if skip: if SA_14: stmt = stmt.filter( sa.sql.literal_column('__group_row_n') > skip) else: stmt = stmt.where( sa.sql.literal_column('__group_row_n') > skip) if limit: if SA_14: stmt = stmt.filter( sa.sql.literal_column('__group_row_n') <= ( (skip or 0) + limit)) else: stmt = stmt.where( sa.sql.literal_column('__group_row_n') <= ( (skip or 0) + limit)) # Done return stmt