Opened 2 years ago

Closed 2 years ago

#33568 closed Bug (duplicate)

Random order doesn't work correctly with aggregation

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 (last modified by Tim Graham)

This query:

    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:

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

Change History (2)

comment:1 by Tim Graham, 2 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 2 years ago

Resolution: → duplicate
Status: new → closed

Duplicate of #26390, fixed in 509d9da26fb92a8d566ec105ff40bb024803ceaa.

Note: See TracTickets for help on using tickets.
Back to Top