Models: annotate and order by random

Using Django
1

I tried to create next sql via django model.

Account.objects.values('plan').annotate(count=Count('plan')).order_by('?')

SELECT
	"accounts"."plan",
	COUNT("accounts"."plan") AS "count"
FROM
	"accounts"
GROUP BY
	"accounts"."plan",
ORDER BY
	RANDOM() ASC
LIMIT 21;

However django create follwoing sql.

SELECT
	"accounts"."plan",
	COUNT("accounts"."plan") AS "count"
FROM
	"accounts"
GROUP BY
	"accounts"."plan",
	RANDOM()
ORDER BY
	RANDOM() ASC
LIMIT 21;

Could you tell me what is wrong here?

2

I donā€™t get those results when I try this. Can you post the Account model?

3

thanks for reply.
Account model is simple table.

class Account(models.Model):
    account_id = models.AutoField(primary_key=True)
    account_name = models.CharField(max_length=255, null=True, blank=True)
    plan = models.CharField(max_length=255, null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True, null=True)
    updated_at = models.DateTimeField(auto_now=True, null=True)

    class Meta:
        db_table = 'accounts'
4

I donā€™t see anything unusual with the model.

What database engine are you using?

5

I use django.db.backends.postgresql

6

Ok, thatā€™s what Iā€™m using.

Iā€™ve got a similar (but not identical) table in my test environment named Author. This is what Iā€™m getting:

In [1]: a=Author.objects.values('name').annotate(cnt=Count('name')).order_by('?')

In [2]: print(a.query)
SELECT "fs_author"."name", COUNT("fs_author"."name") AS "cnt" FROM "fs_author" GROUP BY "fs_author"."name" ORDER BY RANDOM() ASC

This leads me to believe thereā€™s some information associated with this that youā€™re not showing here.

  • What are you looking at that is indicating that Django is creating that sql?
  • How are you running that query? Is that something from your shell? In a view?
  • Is there a custom manager involved?
  • Is there anything else that might be affecting the query being generated?
7

running python manage.py runserver command.
and called from views.py

from .models import Account
from django.db.models import Count

def index(request):
    a = Account.objects.values('plan').annotate(count=Count('plan')).order_by('?')
    print(a.query)

SELECT "accounts"."plan", COUNT("accounts"."plan") AS "count" FROM "accounts" GROUP BY "accounts"."plan", RANDOM() ORDER BY RANDOM() ASC

Iā€™m not using a custom manager.
Itā€™s happening in a simple project I just created, so I canā€™t find any other potential impact.

8

Is this Django 3.2? Python 3.8 or 3.9?

9

Django 3.1 and Python 3.8

10

I verified Iā€™m getting the same results as you with Django 3.1, so this appears to be something that was fixed between 3.1 and 3.2.

I took a quick look through the release notes for 3.2, but I donā€™t see anything quickly that would explain the change in behavior. If youā€™re really interested, you could try searching through the issues to see if you can find when / where the patch was applied.

1 Like
11

thank you!
It resolved by upgrade Django 3.2.

It looks this patch.

FYI: This patch doesnā€™t qualify for backporting to Django 3.1