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)
