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 · Accepted 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

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

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