Select distinct values from a table field
django, django-orm, django-queryset, query-performance
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
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
Problem
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.