Select distinct values from a table field
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 []
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
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')