Question 90271 in PostgreSQL Question posted in
The official documentation can be found here.

How to get a count of objects in a Queryset – Postgresql

I have a model which which has fields like:

class Vehicle(models.Model):
    car_name = models.CharField(max_length=255)
    car_color = models.CharField(max_length=255)

This model has a lot of duplicate values too, I would like distinct to be shown

The queryset gives an output like:

<QuerySet [{'car_name': 'Audi', 'car_color': 'Red'}, {car_name': 'BMW', 'car_color': 'white'}]>
[edit]I want my output Queryset to have another field, which is like a counter for the ouput. If I get two objects out of the query, then a field called ordinal_count also be sent in queryset.

The output I am looking for example:

<QuerySet [{'car_name': 'Audi', 'car_color': 'Red', 'ordinal_count': 1}, {car_name': 'BMW', 'car_color': 'white', 'ordinal_count': 2}, {car_name': 'Jaguar', 'car_color': 'olive green', 'ordinal_count': 3}]>

This is the query which I wrote:

op = (Vehicle.objects.annotate(ordinal_count=Count("car_name", distinct="car_name")).filter(car_color='white', list_display=True).values("car_name","car_color", "ordinal_count"))

This is not giving me the desired input and is also messing up the filter. Should I even be using annotate?

Then I also wrote another query but it fails to run:

NotImplementedError: annotate() + distinct(fields) is not implemented.

Query is:

count = Vehicle.objects.filter(car_color='white', list_display=True).distinct(
      "car_name").annotate(ordinal_count=Count("car_name")).values_list(
        "car_name","car_color", "ordinal_count")
[edit 1] Tried the solution by @trigo, but it gives me an output like(where ordinal_count remains 2). I want ordinal_count to be like a counter for the objects which come in a queryset:

<QuerySet [{'car_name': 'Audi', 'car_color': 'Red', 'ordinal_count': 2}, {car_name': 'BMW', 'car_color': 'white', 'ordinal_count': 2}, {car_name': 'Jaguar', 'car_color': 'olive green', 'ordinal_count': 2}]>

[Update]:

qs = (
        Vehicle.objects.filter(car_color="white", list_display=True)
        .distinct("car_name")
        .annotate(ordinal_count=Window(expression=RowNumber(), partition_by=None))
        .values("slug", "car_name", "car_color","ordinal_count")
    ).order_by('car_name')

the only issue here is annotate works before filtering. So the ordinal_count is messing up. Is there a way to be able to filter first?

edit3

Suppose there are 5 objects in total and I have said I want all white color cars, with distinct car_name.

The output after applying filter, it seems like annotation happens after filter, because of the jumbled up ordinal_count:

<QuerySet [{'car_name': 'Audi', 'car_color': 'white', 'ordinal_count': 3}, {car_name': 'BMW', 'car_color': 'white', 'ordinal_count': 2}, {car_name': 'Jaguar', 'car_color': 'white', 'ordinal_count': 5}]>
Tags:

3

Answers


  1. 0

    [Assumption] : By duplicate you mean only on the attribute "car_name". However, if you mean duplicate on "car_name" and "color" combined, below queryset shall not work. Let me know if this is the case, shall provide other solution for it.

    The below queryset shall give you your desired output.

    Vehicle.objects.filter().values("car_name").annotate(ordinal_count=Count("id")).order_by("car_name")

    [Note]: If your database is PostgreSQL, you can use distinct() however in other database this is hacky way to achieve the same.

    In complex queries, you would need to write a sql query directly.

    [EDIT 1]
    Ok, so basically ordinal_count is NOT the number of times the car_name has occurred but just the counter. Right?
    As per your expected output which you provided, i.e.

    <QuerySet [{'car_name': 'Audi', 'car_color': 'Red', 'ordinal_count': 1}, {car_name': 'BMW', 'car_color': 'white', 'ordinal_count': 2}, {car_name': 'Jaguar', 'car_color': 'olive green', 'ordinal_count': 3}]>

    As per your sample output above, by ordinal_count you only mean the counter (i.e.) index of the array + 1 ? That is the only conclusion I can reach as per your example. For this you may not need to annotate at all.

    Login or Signup to reply.
  2. 0

    I do not have a complete example but there is a Window expression (inside you can use RowNumber()) available in django models that could help you:

    from django.db.models.expressions import Window
from django.db.models.functions import RowNumber

query = Vehicle.objects.values(...).annotate(row_number=Window(expression=RowNumber()))

    https://docs.djangoproject.com/en/4.1/ref/models/expressions/#window-functions

    Login or Signup to reply.
  3. 0 
    from django.db.models import Count, Window, F

Vehicle.objects.distinct('car_name').annotate(ordinal_count=Window(
    expression=Count("id"),
    partition_by=[F("car_name")]
)).values(
    car_name,
    car_color,
    ordinal_count
).order_by('ordinal_count')

    This query results following
    [{"car_name": "BMW", "car_color": "black", "ordinal_count": 5}...]

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search