Opened 4 years ago

Closed 4 years ago

#32963 closed Bug (duplicate)

Combination of filter on the annotation and random order.

Reported by: mohamad 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 mohamad ali Mehdizadeh)

I have a query like:
In [6]: Shop.objects\

...: .annotate(product_count = Count('ShopProduct'))\
...: .filter(product_countgt=1)\
...: .order_by('?')

Out[6]: <QuerySet []>

but the out put is empty!
if i remove order_by('?') it works, and the same for the filter(product_countgt=1).
I get the SQL from connection.queries:

SELECT ..., COUNT("nakhll_market_product"."ID") AS "product_count" FROM "nakhll_market_shop" LEFT OUTER JOIN "nakhll_market_product" ON ("nakhll_market_shop"."ID" = "nakhll_market_product"."FK_Shop_id") GROUP BY "nakhll_market_shop"."ID", RANDOM() HAVING COUNT("nakhll_market_product"."ID") > 1 ORDER BY RANDOM() ASC

but this SQL works correctly:

SELECT
... ,
COUNT("nakhll_market_product"."ID") AS "product_count"
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()

Change History (4)

comment:1 by mohamad ali Mehdizadeh, 4 years ago

Description: modified (diff)

comment:2 by mohamad ali Mehdizadeh, 4 years ago

Description: modified (diff)

comment:3 by mohamad ali Mehdizadeh, 4 years ago

The difference is here:

ORM:
GROUP BY "nakhll_market_shop"."ID" , RANDOM() HAVING COUNT("nakhll_market_product"."ID") > 1 ORDER BY RANDOM()

CORRECT:
GROUP BY "nakhll_market_shop"."ID" HAVING COUNT("nakhll_market_product"."ID") > 1 ORDER BY RANDOM()

comment:4 by Mariusz Felisiak, 4 years ago

Resolution: → duplicate
Status: new → closed
Summary: Bug: combination of filter on the annotation and random order. → Combination of filter on the annotation and random order.

Duplicate of #26390 (fixed in Django 3.2).

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