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

User · Answer

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

User · Answer

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  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 dictionnary  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

User · Answer

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

[django] Select distinct values from a table field

Examples related to django

Examples related to django-queryset

Examples related to django-orm

Examples related to query-performance