[Django]-Select distinct values from a table field

by

304👍

✅

Say your model is ‘Shop’

class Shop(models.Model):
    street = models.CharField(max_length=150)
    city = models.CharField(max_length=150)

    # some of your models may have explicit ordering
    class Meta:
        ordering = ('city',)

Since you may have the Meta class ordering attribute set (which is tuple or a list), you can use order_by() without parameters to clear any ordering when using distinct(). See the documentation under order_by()

If you don’t want any ordering to be applied to a query, not even the default ordering, call order_by() with no parameters.

and distinct() in the note where it discusses issues with using distinct() with ordering.

To query your DB, you just have to call:

models.Shop.objects.order_by().values('city').distinct()

It returns a dictionary

or

models.Shop.objects.order_by().values_list('city').distinct()

This one returns a ValuesListQuerySet which you can cast to a list.
You can also add flat=True to values_list to flatten the results.

See also: Get distinct values of Queryset by field

👤jujule

29👍

In addition to the still very relevant answer of jujule, I find it quite important to also be aware of the implications of order_by() on distinct("field_name") queries. This is, however, a Postgres only feature!

If you are using Postgres and if you define a field name that the query should be distinct for, then order_by() needs to begin with the same field name (or field names) in the same sequence (there may be more fields afterward).

Note

When you specify field names, you must provide an order_by() in the
QuerySet, and the fields in order_by() must start with the fields in
distinct(), in the same order.

For example, SELECT DISTINCT ON (a) gives you the first row for each
value in column a. If you don’t specify an order, you’ll get some
arbitrary row.

If you want to e.g. extract a list of cities that you know shops in, the example of jujule would have to be adapted to this:

# returns an iterable Queryset of cities.
models.Shop.objects.order_by('city').values_list('city', flat=True).distinct('city')
👤ingofreyer

3👍

By example:

# select distinct code from Platform where id in ( select platform__id from Build where product=p)
pl_ids = Build.objects.values('platform__id').filter(product=p)
platforms = Platform.objects.values_list('code', flat=True).filter(id__in=pl_ids).distinct('code')
platforms = list(platforms) if platforms else []
👤rogers

2👍

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; using distinct() we can get Unique data.

event = Event.objects.values(‘item_event_type’).distinct()
serializer= ItemEventTypeSerializer(event,many=True)
return Response(serializer.data)

👤Jitendra Meena

1👍

If you don’t use PostgreSQL and you just want to get and distinct with only one specific field you can use

MyModel.objects.values('name').annotate(Count('id')).order_by()

this queryset return us rows that their ‘name’ field is unique with the count of the rows that have same name

<QuerySet [{'name': 'a', 'id__count': 2}, {'name': 'b', 'id__count': 2}, {'name': 'c', 'id__count': 3}>

I know the returned data is not complete and sometimes is not satisfying but sometimes it’s useful

document reference

👤Mahdi mehrabi

Leave a comment