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
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?
What does the model look like?
I have a model like this:
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)
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
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
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.
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.