django django-queryset django-orm query-performance

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.

Solution

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

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.