async def test_brands_raw_prefetch_limited_products(self):
if Product._meta.db.capabilities.dialect == "mysql":
raise test.SkipTest(
"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
)
await create_store_objects()
raw_subquery = """
(select "U1"."id" "0"
from "store_product" "U1"
where "U1"."brand_id"={context.top.table}."brand_id"
order by "U1"."id" asc
limit 2)
"""
subquery = Product.raw(raw_subquery)
prefetch = Prefetch('products',
queryset=Product.filter(id__in=Subquery(subquery)))
brands_fetched = await Brand.all().order_by('name').prefetch_related(
prefetch)
brands_distilled = [{
'name': b.name,
'products': {p.name
for p in b.products}
} for b in brands_fetched]
self.assertEqual(brands_distilled, [
{
'name': 'brand_1',
'products': {'product_01'}
},
{
'name': 'brand_2',
'products': {'product_02', 'product_03'}
},
{
'name': 'brand_3',
'products': {'product_04', 'product_05'}
},
{
'name': 'brand_4',
'products': {'product_07', 'product_08'}
},
{
'name': 'brand_5',
'products': {'product_11', 'product_12'}
},
{
'name': 'brand_6',
'products': {'product_16', 'product_17'}
},
])
async def test_ordering_annotation_aggregations_m2o_values(self):
await create_store_objects()
products = Product.annotate(cnt=Count('brand')).order_by(
"-cnt", "name").values('cnt', 'name').limit(5)
products_fetched = await products
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT '
'COUNT("brand_id") "cnt","name" "name" '
'FROM "store_product" '
'GROUP BY "id" '
'ORDER BY "cnt" DESC,"name" ASC '
'LIMIT 5')
self.assertEqual(products_fetched, [{
'name': 'product_01',
'cnt': 1
}, {
'name': 'product_02',
'cnt': 1
}, {
'name': 'product_03',
'cnt': 1
}, {
'name': 'product_04',
'cnt': 1
}, {
'name': 'product_05',
'cnt': 1
}])
async def test_brands_prefetch_limited_products(self):
if Product._meta.db.capabilities.dialect == "mysql":
raise test.SkipTest(
"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
)
await create_store_objects()
subquery = Product.filter(
brand=OuterRef('brand')).limit(3).values_list('id', flat=True)
prefetch = Prefetch('products',
queryset=Product.filter(id__in=Subquery(subquery)))
brands_fetched = await Brand.all().order_by('name').prefetch_related(
prefetch)
brands_distilled = [{
'name': b.name,
'products': {p.name
for p in b.products}
} for b in brands_fetched]
self.assertEqual(brands_distilled, [
{
'name': 'brand_1',
'products': {'product_01'}
},
{
'name': 'brand_2',
'products': {'product_02', 'product_03'}
},
{
'name': 'brand_3',
'products': {'product_04', 'product_05', 'product_06'}
},
{
'name': 'brand_4',
'products': {'product_07', 'product_08', 'product_09'}
},
{
'name': 'brand_5',
'products': {'product_11', 'product_12', 'product_13'}
},
{
'name': 'brand_6',
'products': {'product_16', 'product_17', 'product_18'}
},
])
async def test_annotation_f(self):
products = Product.all().annotate(new_order=F('id') * 5)
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string,
'SELECT "id","name","price","brand_id","vendor_id","id"*5 "new_order" '
'FROM "store_product" '
'ORDER BY "id" ASC')
async def test_ordering_functions(self):
products = Product.all().order_by((F('id') * 7) % 143).limit(20)
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT "id","name","price","brand_id","vendor_id" '
'FROM "store_product" '
'ORDER BY MOD("id"*7,143) ASC '
'LIMIT 20')
async def test_random_ordering(self):
products = Product.all().order_by(RandomOrdering()).limit(20)
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT "id","name","price","brand_id","vendor_id" '
'FROM "store_product" '
'ORDER BY RANDOM() '
'LIMIT 20')
async def test_ordering_aggregations_m2o(self):
products = Product.all().order_by(-Count('brand')).limit(20)
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT "id","name","price","brand_id","vendor_id" '
'FROM "store_product" '
'GROUP BY "id" '
'ORDER BY COUNT("brand_id") DESC '
'LIMIT 20')
async def test_annotation(self):
products = Product.filter(
brand_id=OuterRef('id')).limit(1).values_list('name', flat=True)
brands = Brand.annotate(product_name=Subquery(products))
query_string = brands.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT "id","name","image_id",('
'SELECT "U1"."name" "0" FROM "store_product" "U1" '
'WHERE "U1"."brand_id"="store_brand"."id" ORDER BY "U1"."id" ASC LIMIT 1) "product_name" '
'FROM "store_brand" ORDER BY "id" ASC')