How Query class work?

Django Internals
1

I have a query like

Model.objects.values('column').annotate(column_count = Count('column')).filter(column_count__gt=1)

but it doesn’t work correct, so I check it’s SQL

SELECT "table"."column", COUNT("table"."column") AS "column_count" FROM "table" GROUP BY  "table"."name", "table"."date_created"

So it adds “date_created” column to “GROUP BY” unexpectedly.
I check django source code and find clone.query.set_group_by() in django.db.models.query.QuerySet and check set_group_by function but it only set self.group_by. I can’t find it out when is “date_created” is added to query?

2

What does the model look like?

3

I have a model like this:

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.IntegerField(_("Price"))
    inventory = models.IntegerField(_("Inventory"))
    description = models.TextField(_("Description"))
    created_at = models.DateTimeField(_("Created at"), auto_now_add=True)
    updated_at = models.DateTimeField(_("Updated at"), auto_now=True)


    class Meta:
        ordering = ('created_at', 'name',)
        verbose_name = _("Product")
        verbose_name_plural = _("Products")

And I want to get products with repeated names so I write a query like:

products = Product.objects.values('name').annotate(name_count = Count('name')).filter(name_count__gt=1)

And it returns an empty QuerySet, I check its generated SQL query by print(products.query):

SELECT "sample_product"."name", COUNT("sample_product"."name") AS "name_count" FROM "sample_product" GROUP BY "sample_product"."name", "sample_product"."created_at" HAVING COUNT("sample_product"."name") > 1

I check set_group_by function and see it only sets self.group_by value, I expect it to change some SQL parts, after that, I check the __str__ method and I see QuerySet generate its Query object and it’s passed to SQLCompiler, therefrom Query attributes compiler generates final SQL.

I see this behavior is modified in Django 4 and meta orders aren’t considered in group_by.

4

I’m not following what your question or issue is here that you’re trying to get answered.

The SQL you’re showing appears appropriate for the query you’ve written.

I’ll point out that the query attribute on a queryset does not show you the SQL query that is sent to the database. It’s a representation of the query object that is eventually going to be used to build the SQL.

If you want to look at the real queries that were generated, you need to look at the django.db.connection.queries list. (The actual SQL is not produced until the queryset is executed.) This is because the precise SQL may not be able to be known until it’s identified what database engine is going to be used, and it’s the database engine that creates the SQL from the query object.

Now, having said all that, the differences between the two do tend to be small - they’re usually close enough that you don’t need to worry about those differences. However, you should always be aware of that distinction.