id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33568	Random order doesn't work correctly with aggregation	Mohammad Ali Mehdizadeh	nobody	"This query:
{{{ #!python
    Shop.objects\
    .filter(Publish = True, Available = True)\
    .annotate(product_count = Count('ShopProduct'))\
    .filter(product_count__gt=1)\
    .order_by('?').values('ID')[:12]
}}}
and ORM generate this SQL:
{{{ #!sql
SELECT ""nakhll_market_shop"".""ID"" FROM ""nakhll_market_shop"" LEFT OUTER JOIN ""nakhll_market_product"" ON (""nakhll_market_shop"".""ID"" = ""nakhll_market_product"".""FK_Shop_id"") WHERE (""nakhll_market_shop"".""Available"" AND ""nakhll_market_shop"".""Publish"") GROUP BY ""nakhll_market_shop"".""ID"", RANDOM() HAVING COUNT(""nakhll_market_product"".""ID"") > 1 ORDER BY RANDOM() ASC LIMIT 12
}}}
but it does not return any shop,
the correct SQL query is:
{{{ #!sql
SELECT ""nakhll_market_shop"".""ID"" FROM ""nakhll_market_shop"" LEFT OUTER JOIN ""nakhll_market_product"" ON (""nakhll_market_shop"".""ID"" = ""nakhll_market_product"".""FK_Shop_id"") WHERE (""nakhll_market_shop"".""Available"" AND ""nakhll_market_shop"".""Publish"") GROUP BY ""nakhll_market_shop"".""ID"" HAVING COUNT(""nakhll_market_product"".""ID"") > 1 ORDER BY RANDOM() ASC LIMIT 12
}}}
"	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate	Random + Group By		Unreviewed	0	0	0	0	0	0