Select distinct values from a table field

Question

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.

Solution 1

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

Solution 2

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

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

Comments

[alj]

Actually that works. However! I couldn't get it to work on all my models. Weidly, it worked on some but not others. For those that have a Meta ordering it doesn't work. So, you have to clear the ordering on the queryset first. models.Shop.objects.order_by().values('city').distinct()

[dheerosaur]

It is important to note that values_list doesn't actually return a list. It returns something like a queryset. I found it useful to always use list() around values_list calls.

[Peter Kilczuk]

values_list returns ValuesListQuerySet which is an iterator. Casting to list might be handy, but can also strike performance when all rows have to be evaluated at once, especially with large data sets.

[hobs]

The Meta: ordering = () "feature" of django orm and objects.distinct() vs. objects.ordering().distinct() caused us hours of confusion. There should be a consumer-safety warning sticker on that product;) We may institute a no-Meta-ordering-attribute policy to prevent the head-scratching in the future.

[Mark Mikofski]

You can turn off Meta class ordering and resolve issues with distinct by using order_by() with no parameters. It's in the QuerySet API docs 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."

[comiventor]

I am not sure why order_by came into picture. The question was related to getting distinct values and that can be achieved without any kind of ordering involved. Shop.objects.values(myfieldname).distinct()

[JD Gamboa]

The first option also returns a queryset, so it's not really what's needed.

[Denis]

Thank you very much for .distinct("field_name")!