CODEWITHSUNDEEP
djangodjango-querysetdjango-ormquery-performance
alj
alj

Reputation: 3039

Select distinct values from a table field

I'm struggling getting my head around the Django's ORM. What I want to do is get a list of distinct values within a field on my table .... the equivalent of one of the following:

SELECT DISTINCT myfieldname FROM mytable

(or alternatively)

SELECT myfieldname FROM mytable GROUP BY myfieldname

I'd at least like to do it the Django way before resorting to raw sql. For example, with a table:

id, street, city

1, Main Street, Hull

2, Other Street, Hull

3, Bibble Way, Leicester

4, Another Way, Leicester

5, High Street, Londidium

I'd like to get:

Hull, Leicester, Londidium.

Upvotes: 179

Views: 209966

Answers (5)

Jitendra Meena
Jitendra Meena

Reputation: 21

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)

Upvotes: 2

Mahdi mehrabi
Mahdi mehrabi

Reputation: 1744

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

Upvotes: 2

ingofreyer
ingofreyer

Reputation: 1164

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')

Upvotes: 31

jujule
jujule

Reputation: 11541

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

Upvotes: 321

rogers
rogers

Reputation: 365

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 []

Upvotes: 3

Related Questions