Opened 3 years ago
Last modified 3 years ago
#33568 closed Bug
Random order doesn't work correctly with aggregation — at Initial Version
| Reported by: | Mohammad Ali Mehdizadeh | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 3.1 |
| Severity: | Normal | Keywords: | Random + Group By |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
This query:
Shop.objects\
.filter(Publish = True, Available = True)\
.annotate(product_count = Count('ShopProduct'))\
.filter(product_countgt=1)\
.order_by('?').values('ID')[:12]
and ORM generate this 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:
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