def get_queryset(self): """ Return the list of items for this handler. The return value must be an iterable and may be an instance of `QuerySet` in which case `QuerySet` specific behavior will be enabled. """ if self.queryset is not None: queryset = self.queryset if isinstance(queryset, Query): queryset = queryset.all() elif self.model is not None: queryset = self.model.query.all() else: raise ImproperlyConfigured( "%(cls)s is missing a QuerySet. Define " "%(cls)s.model, %(cls)s.queryset, or override " "%(cls)s.get_queryset()." % {'cls': self.__class__.__name__}) ordering = self.get_ordering() if ordering: if isinstance(ordering, str): ordering = (ordering, ) queryset = sort_query(queryset, *ordering) return queryset
def test_column_property(self, session, TextItem): TextItem.item_count = sa.orm.column_property( sa.select([sa.func.count("1")]).select_from(TextItem.__table__).label("item_count") ) query = sort_query(session.query(TextItem), "item_count") assert_contains("ORDER BY item_count", query)
def test_with_mapper_and_column_property(self): class Apple(self.Base): __tablename__ = 'apple' id = sa.Column(sa.Integer, primary_key=True) article_id = sa.Column(sa.Integer, sa.ForeignKey(self.Article.id)) self.Article.apples = sa.orm.relationship(Apple) self.Article.apple_count = sa.orm.column_property( sa.select([sa.func.count(Apple.id)]) .where(Apple.article_id == self.Article.id) .correlate(self.Article.__table__) .label('apple_count'), deferred=True ) query = ( self.session.query(sa.inspect(self.Article)) .outerjoin(self.Article.apples) .options( sa.orm.undefer(self.Article.apple_count) ) .options(sa.orm.contains_eager(self.Article.apples)) ) query = sort_query(query, 'apple_count') assert 'ORDER BY apple_count' in str(query)
def test_relation_hybrid_property(self): query = ( self.session.query(self.Article) .join(self.Article.category) ).group_by(self.Article.id) query = sort_query(query, '-category-articles_count') assert_contains('ORDER BY (SELECT count(article.id) AS count_1', query)
def test_join(self): query = ( self.session.query(self.Article) .join(self.Article.category) ) query = sort_query(query, 'name', silent=False) assert 'ORDER BY article.name ASC' in str(query)
def test_join(self, session, Article): query = ( session.query(Article) .join(Article.category) ) query = sort_query(query, 'name', silent=False) assert_contains('ORDER BY article.name ASC', query)
def test_aliased_concat_hybrid_property(self, session, Article, Category): alias = sa.orm.aliased(Category, name='aliased') query = (session.query(Article).outerjoin( alias, Article.category).options( sa.orm.contains_eager(Article.category, alias=alias))) query = sort_query(query, 'aliased-full_name') assert_contains('concat(aliased.title, :param_1, aliased.name)', query)
def test_column_property(self, session, TextItem): TextItem.item_count = sa.orm.column_property( sa.select([sa.func.count('1')], ).select_from( TextItem.__table__).label('item_count')) query = sort_query(session.query(TextItem), 'item_count') assert_contains('ORDER BY item_count', query)
def test_aliased_hybrid_property(self): alias = sa.orm.aliased(self.Category, name='categories') query = (self.session.query(self.Article).outerjoin( alias, self.Article.category).options( sa.orm.contains_eager(self.Article.category, alias=alias))) query = sort_query(query, '-categories-articles_count') assert 'ORDER BY (SELECT count(article.id) AS count_1' in str(query)
def test_hybrid_property_descending(self): query = self.session.query(self.Category) query = sort_query(query, '-articles_count') assert ( 'ORDER BY (SELECT count(article.id) AS count_1' ) in str(query) assert ' DESC' in str(query)
def test_relation_hybrid_property(self): query = ( self.session.query(self.Article) .join(self.Article.category) ).group_by(self.Article.id) query = sort_query(query, '-category-articles_count') assert_contains('ORDER BY (SELECT count(article.id) AS count_1', query)
def test_with_mapper_and_column_property(self): class Apple(self.Base): __tablename__ = 'apple' id = sa.Column(sa.Integer, primary_key=True) article_id = sa.Column(sa.Integer, sa.ForeignKey(self.Article.id)) self.Article.apples = sa.orm.relationship(Apple) self.Article.apple_count = sa.orm.column_property( sa.select([sa.func.count(Apple.id)]) .where(Apple.article_id == self.Article.id) .correlate(self.Article.__table__) .label('apple_count'), deferred=True ) query = ( self.session.query(sa.inspect(self.Article)) .outerjoin(self.Article.apples) .options( sa.orm.undefer(self.Article.apple_count) ) .options(sa.orm.contains_eager(self.Article.apples)) ) query = sort_query(query, 'apple_count') assert 'ORDER BY apple_count' in str(query)
def test_join(self, session, Article): query = ( session.query(Article) .join(Article.category) ) query = sort_query(query, 'name', silent=False) assert_contains('ORDER BY article.name ASC', query)
def test_column_property(self): self.TextItem.item_count = sa.orm.column_property( sa.select([sa.func.count('1')], ).select_from( self.TextItem.__table__).label('item_count')) query = sort_query(self.session.query(self.TextItem), 'item_count') assert_contains('ORDER BY (SELECT count(:param_2) AS count_2', query)
def test_without_sort_param_returns_the_query_object_untouched( self, session, Article ): query = session.query(Article) query = sort_query(query, '') assert query == query
def test_without_sort_param_returns_the_query_object_untouched( self, session, Article ): query = session.query(Article) query = sort_query(query, '') assert query == query
def test_assigned_hybrid_property(self, session, Article): def getter(self): return self.name Article.some_hybrid = sa.ext.hybrid.hybrid_property(fget=getter) query = session.query(Article) query = sort_query(query, "some_hybrid") assert_contains("ORDER BY article.name ASC", query)
def test_column_property_descending(self, session, Article, Category): Category.article_count = sa.orm.column_property( sa.select([sa.func.count(Article.id)]).where(Article.category_id == Category.id).label("article_count") ) query = session.query(Category) query = sort_query(query, "-article_count") assert_contains("article_count DESC", query)
def test_with_existing_column(self): query = sort_query( self.session.query( sa.orm.with_polymorphic(self.TextItem, [self.Article]) ), 'category' ) assert 'ORDER BY' in str(query)
def test_with_unknown_column(self): query = sort_query( self.session.query( sa.orm.with_polymorphic(self.TextItem, [self.BlogPost]) ), 'category' ) assert 'ORDER BY' not in str(query)
def test_relation_hybrid_property(self): query = ( self.session.query(self.Article) .join(self.Article.category) .correlate(self.Article.__table__) ) query = sort_query(query, '-category-articles_count') assert 'ORDER BY (SELECT count(article.id) AS count_1' in str(query)
def test_assigned_hybrid_property(self, session, Article): def getter(self): return self.name Article.some_hybrid = sa.ext.hybrid.hybrid_property(fget=getter) query = session.query(Article) query = sort_query(query, 'some_hybrid') assert_contains('ORDER BY article.name ASC', query)
def test_column_property_descending(self, session, Article, Category): Category.article_count = sa.orm.column_property( sa.select([sa.func.count(Article.id)]).where( Article.category_id == Category.id).label('article_count')) query = session.query(Category) query = sort_query(query, '-article_count') assert_contains('article_count DESC', query)
def test_with_unknown_column(self): query = sort_query( self.session.query( sa.orm.with_polymorphic(self.TextItem, [self.BlogPost]) ), 'category' ) assert 'ORDER BY' not in str(query)
def test_with_existing_column(self): query = sort_query( self.session.query( sa.orm.with_polymorphic(self.TextItem, [self.Article]) ), 'category' ) assert 'ORDER BY' in str(query)
def test_aliased_hybrid_property(self): alias = sa.orm.aliased(self.Category, name="categories") query = ( self.session.query(self.Article) .outerjoin(alias, self.Article.category) .options(sa.orm.contains_eager(self.Article.category, alias=alias)) ) query = sort_query(query, "-categories-articles_count") assert "ORDER BY (SELECT count(article.id) AS count_1" in str(query)
def test_aliased_concat_hybrid_property(self, session, Article, Category): alias = sa.orm.aliased(Category, name="aliased") query = ( session.query(Article) .outerjoin(alias, Article.category) .options(sa.orm.contains_eager(Article.category, alias=alias)) ) query = sort_query(query, "aliased-full_name") assert_contains("concat(aliased.title, %(concat_1)s, aliased.name)", query)
def test_column_property(self): self.Category.article_count = sa.orm.column_property( sa.select([sa.func.count(self.Article.id) ]).where(self.Article.category_id == self.Category.id).label('article_count')) query = self.session.query(self.Category) query = sort_query(query, 'article_count') assert_contains('article_count ASC', query)
def test_aliased_joined_entity(self): alias = sa.orm.aliased(self.Category, name='categories') query = self.session.query( self.Article ).join( alias, self.Article.category ) query = sort_query(query, '-categories-name') assert 'ORDER BY categories.name DESC' in str(query)
def test_sorting_by_country(db_session, next): _create_people(db_session) # when: query = db_session.query(Person).join(Country).join(JobType) result = sort_query(query, next[0]).all() # then: assert result[0].name == next[1]
def test_aliased_joined_entity(self, session, Article, Category): alias = sa.orm.aliased(Category, name='categories') query = session.query( Article ).join( alias, Article.category ) query = sort_query(query, '-categories-name') assert_contains('ORDER BY categories.name DESC', query)
def test_column_property_descending(self): self.Category.article_count = sa.orm.column_property( sa.select([sa.func.count(self.Article.id) ]).where(self.Article.category_id == self.Category.id).label('article_count')) query = self.session.query(self.Category) sorted_query = sort_query(query, '-article_count') assert 'article_count DESC' in str(sorted_query)
def test_aliased_hybrid_property(self, session, Article, Category): alias = sa.orm.aliased(Category, name='categories') query = (session.query(Article).outerjoin( alias, Article.category).options( sa.orm.contains_eager(Article.category, alias=alias))).group_by( alias.id, Article.id) query = sort_query(query, '-categories-articles_count') assert_contains('ORDER BY (SELECT count(article.id) AS count_1', query)
def test_aliased_joined_entity(self, session, Article, Category): alias = sa.orm.aliased(Category, name='categories') query = session.query( Article ).join( alias, Article.category ) query = sort_query(query, '-categories-name') assert_contains('ORDER BY categories.name DESC', query)
def test_aliased_hybrid_property(self, session, Article, Category): alias = sa.orm.aliased(Category, name="categories") query = ( session.query(Article) .outerjoin(alias, Article.category) .options(sa.orm.contains_eager(Article.category, alias=alias)) ).group_by(alias.id, Article.id) query = sort_query(query, "-categories-articles_count") assert_contains("ORDER BY (SELECT count(article.id) AS count_1", query)
def test_column_property(self): self.Category.article_count = sa.orm.column_property( sa.select([sa.func.count(self.Article.id)]) .where(self.Article.category_id == self.Category.id) .label('article_count') ) query = self.session.query(self.Category) sorted_query = sort_query(query, 'article_count') assert 'article_count ASC' in str(sorted_query)
def test_assigned_hybrid_property(self): def getter(self): return self.name self.Article.some_hybrid = sa.ext.hybrid.hybrid_property( fget=getter ) query = self.session.query(self.Article) query = sort_query(query, 'some_hybrid') assert_contains('ORDER BY article.name ASC', query)
def test_column_property_descending(self): self.Category.article_count = sa.orm.column_property( sa.select([sa.func.count(self.Article.id)]) .where(self.Article.category_id == self.Category.id) .label('article_count') ) query = self.session.query(self.Category) query = sort_query(query, '-article_count') assert_contains('article_count DESC', query)
def test_subqueried_scalar(self, session, Article, Category): article_count = ( sa.sql.select([sa.func.count(Article.id)], from_obj=[Article.__table__]) .where(Article.category_id == Category.id) .correlate(Category.__table__) ) query = session.query(Category, article_count.label("articles")) query = sort_query(query, "-articles") assert_contains("ORDER BY articles DESC", query)
def test_subqueried_scalar(self, session, Article, Category): article_count = (sa.sql.select( [sa.func.count(Article.id)], from_obj=[Article.__table__ ]).where(Article.category_id == Category.id).correlate( Category.__table__)) query = session.query(Category, article_count.label('articles')) query = sort_query(query, '-articles') assert_contains('ORDER BY articles DESC', query)
def test_subqueried_scalar(self): article_count = ( sa.sql.select([sa.func.count(self.Article.id)], from_obj=[self.Article.__table__]) .where(self.Article.category_id == self.Category.id) .correlate(self.Category.__table__) ) query = self.session.query(self.Category, article_count.label("articles")) query = sort_query(query, "-articles") assert "ORDER BY articles DESC" in str(query)
def test_column_property_descending(self): self.Category.article_count = sa.orm.column_property( sa.select([sa.func.count(self.Article.id)]) .where(self.Article.category_id == self.Category.id) .label("article_count") ) query = self.session.query(self.Category) sorted_query = sort_query(query, "-article_count") assert "article_count DESC" in str(sorted_query)
def test_column_property(self, session, Article, Category): Category.article_count = sa.orm.column_property( sa.select([sa.func.count(Article.id)]) .where(Article.category_id == Category.id) .label('article_count') ) query = session.query(Category) query = sort_query(query, 'article_count') assert_contains('article_count ASC', query)
def test_subqueried_scalar(self): article_count = (sa.sql.select( [sa.func.count(self.Article.id)], from_obj=[ self.Article.__table__ ]).where(self.Article.category_id == self.Category.id).correlate( self.Category.__table__)) query = self.session.query(self.Category, article_count.label('articles')) query = sort_query(query, '-articles') assert 'ORDER BY articles DESC' in str(query)
def order_results(results, default_order, orders_whitelist): """ Orders the given results with the sort order as requested in the "order" request query parameter or the given default order. """ # See if a particular order has been requested order = request.args.get('order', '').strip() or default_order # and if it matches a long-form for related fields, falling # back to the default order selected_order = orders_whitelist.get(order, default_order) # The query may already have an ORDER BY statement attached # so we clear it here and apply the selected order return sort_query(results.order_by(None), selected_order)
def order_results(results, default_order='-created_at'): """ Orders the given results with the sort order as requested in the "order" request query parameter or the given default order. """ # See if a particular order has been requested order = request.args.get('order', '').strip() or default_order # and if it matches a long-form for related fields, falling # back to the default order selected_order = order_map.get(order, default_order) # The query may already have an ORDER BY statement attached # so we clear it here and apply the selected order return sort_query(results.order_by(None), selected_order)
def test_aliased_hybrid_property(self): alias = sa.orm.aliased( self.Category, name='categories' ) query = ( self.session.query(self.Article) .outerjoin(alias, self.Article.category) .options( sa.orm.contains_eager(self.Article.category, alias=alias) ) ).group_by(alias.id, self.Article.id) query = sort_query(query, '-categories-articles_count') assert_contains('ORDER BY (SELECT count(article.id) AS count_1', query)
def test_aliased_concat_hybrid_property(self): alias = sa.orm.aliased( self.Category, name='aliased' ) query = ( self.session.query(self.Article) .outerjoin(alias, self.Article.category) .options( sa.orm.contains_eager(self.Article.category, alias=alias) ) ) query = sort_query(query, 'aliased-full_name') assert_contains( 'concat(aliased.title, :param_1, aliased.name)', query )
def test_column_property(self): self.TextItem.item_count = sa.orm.column_property( sa.select( [ sa.func.count('1') ], ) .select_from(self.TextItem.__table__) .label('item_count') ) query = sort_query( self.session.query(self.TextItem), 'item_count' ) assert_contains('ORDER BY item_count', query)
def test_filtering_by_company_and_sorting_by_country(db_session): # given: a bunch of people _create_people(db_session) # because we're not sorting by company there's no # need to create an alias, but it would be # advisable to do so just in case query = db_session.\ query(ConferencePerson).\ join(SourceCountry).\ join(Company).\ filter(Company.name == 'google') # when: sorting the query by country's name result = sort_query(query, 'source_country-name').all() # then: we should get the expected person assert result[0].name == 'Peter'
def test_sorting_by_country(db_session, next): # given: a bunch of people _create_people(db_session) # the name of the property we want to sort by must # match the name of the name of the joined table # here we've created an alias because the table is # named 'companies' and the sort_by property is `company` joined_company = aliased(Company, name='company') query = db_session.\ query(ConferencePerson).\ join(SourceCountry).\ join(joined_company) # when: sorting by the current criteria result = sort_query(query, next[0]).all() # then: we should get the expected result assert result[0].name == next[1]
def order_results(results, default_order, allowed_orders, fallback=True): """ Orders the given results with the sort order as requested in the "order" request query parameter or the given default order. """ # See if a particular order has been requested requested_order = request.args.get('order', '').strip() # and if not (and no fallback is wanted) return results as is if not requested_order and not fallback: return results # and if it matches a long-form for related fields, falling # back to the default order selected_order = allowed_orders.get(requested_order, None) if selected_order is None and fallback: selected_order = default_order # The query may already have an ORDER BY statement attached # so we clear it here and apply the selected order return sort_query(results.order_by(None), selected_order)
def order_results(results, default_order, allowed_orders, fallback=True): """ Orders the given results with the sort order as requested in the "order" request query parameter or the given default order. """ # See if a particular order has been requested requested_order = request.args.get("order", "").strip() # and if not (and no fallback is wanted) return results as is if not requested_order and not fallback: return results # and if it matches a long-form for related fields, falling # back to the default order selected_order = allowed_orders.get(requested_order, None) if selected_order is None and fallback: selected_order = default_order # The query may already have an ORDER BY statement attached # so we clear it here and apply the selected order return sort_query(results.order_by(None), selected_order)
def get_queryset(self): """ Return the list of items for this handler. The return value must be an instance of `sqlalchemy.orm.Query`. """ if isinstance(self.queryset, Query): queryset = self.queryset elif self.model is not None: queryset = self.model.query else: raise ImproperlyConfigured( "%(cls)s is missing a queryset. Define " "%(cls)s.model, %(cls)s.queryset, or override " "%(cls)s.get_queryset()." % {'cls': self.__class__.__name__}) ordering = self.get_ordering() if ordering: if isinstance(ordering, str): ordering = (ordering, ) queryset = sort_query(queryset, *ordering) return queryset
def test_with_mapper_and_column_property(self, session, Base, Article): class Apple(Base): __tablename__ = "apple" id = sa.Column(sa.Integer, primary_key=True) article_id = sa.Column(sa.Integer, sa.ForeignKey(Article.id)) Article.apples = sa.orm.relationship(Apple) Article.apple_count = sa.orm.column_property( sa.select([sa.func.count(Apple.id)]) .where(Apple.article_id == Article.id) .correlate(Article.__table__) .label("apple_count"), deferred=True, ) query = ( session.query(sa.inspect(Article)) .outerjoin(Article.apples) .options(sa.orm.undefer(Article.apple_count)) .options(sa.orm.contains_eager(Article.apples)) ) query = sort_query(query, "apple_count") assert "ORDER BY apple_count" in str(query)
def test_multiple_columns(self): query = self.session.query(self.Article) sorted_query = sort_query(query, 'name', 'id') assert 'article.name ASC, article.id ASC' in str(sorted_query)