Models: annotate and order by random

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?

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

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'

I don’t see anything unusual with the model.

What database engine are you using?

I use django.db.backends.postgresql

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?

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.

Is this Django 3.2? Python 3.8 or 3.9?

Django 3.1 and Python 3.8

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

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