By jamida


2010-10-04 00:14:20 8 Comments

I'm curious if there's any way to do a query in Django that's not a "SELECT * FROM..." underneath. I'm trying to do a "SELECT DISTINCT columnName FROM ..." instead.

Specifically I have a model that looks like:

class ProductOrder(models.Model):
   Product  = models.CharField(max_length=20, promary_key=True)
   Category = models.CharField(max_length=30)
   Rank = models.IntegerField()

where the Rank is a rank within a Category. I'd like to be able to iterate over all the Categories doing some operation on each rank within that category.

I'd like to first get a list of all the categories in the system and then query for all products in that category and repeat until every category is processed.

I'd rather avoid raw SQL, but if I have to go there, that'd be fine. Though I've never coded raw SQL in Django/Python before.

4 comments

@SuperNova 2015-10-15 08:31:43

User order by with that field, and then do distinct.

ProductOrder.objects.order_by('category').values_list('category', flat=True).distinct()

@Mark Chackerian 2015-02-02 23:00:23

The other answers are fine, but this is a little cleaner, in that it only gives the values like you would get from a DISTINCT query, without any cruft from Django.

>>> set(ProductOrder.objects.values_list('category', flat=True))
{u'category1', u'category2', u'category3', u'category4'}

or

>>> list(set(ProductOrder.objects.values_list('category', flat=True)))
[u'category1', u'category2', u'category3', u'category4']

And, it works without PostgreSQL.

This is less efficient than using a .distinct(), presuming that DISTINCT in your database is faster than a python set, but it's great for noodling around the shell.

@mehmet 2016-06-29 22:10:36

values_list does not put DISTINCT in the sql query, so this would bring multiple values if there were.

@Mark Chackerian 2016-06-29 23:11:28

no it doesn't -- that's what set is for

@geekandglitter 2019-06-03 18:58:31

The list option worked great. Thank you

@Wolph 2010-10-04 00:22:09

It's quite simple actually if you're using PostgreSQL, just use distinct(columns).

Productorder.objects.all().distinct('category')

Note that this feature has been included in Django since 1.4

@Bernhard Vallant 2010-10-04 01:27:49

-1: distinct doesn't take any fields as argument, the documentation shows that and also check out the code: code.djangoproject.com/browser/django/trunk/django/db/models‌​/…

@Manoj Govindan 2010-10-04 05:17:28

Lazerscience is correct. Distinct will not accept any column names as arguments.

@Wolph 2010-10-04 07:35:38

@lazerscience, @Manoj Govindan: I'm sorry, you're right. It seems that I have patched Django to add that feature. I've added a link to the patch

@Will Hardy 2011-12-28 16:06:37

This is now in Django SVN and will be in Django 1.4

@holms 2012-03-08 18:22:40

why things can't be always simple as this.. i wish we'd had django 1.4 on our servers already..

@Mark Chackerian 2013-02-04 21:04:00

Note: unless you're using PostgreSQL, you can't give distinct() an argument. Best stick with the accepted solution above.

@Skylar Saveland 2013-02-18 02:02:40

in the tests, this is can_distinct_on_fields which appears to be Postgres-only

@Wolph 2014-04-09 11:28:20

It would be useful if the downvoters could explain the downvotes, the original answer was indeed incorrect but that was rectified about 4 years ago ;)

@Antony Hatchkins 2015-04-23 11:51:52

plus 1, but all() is not necessary here

@Hassan Baig 2016-11-28 12:44:03

Does count() work with distinct('field')?

@Manoj Govindan 2010-10-04 05:30:09

One way to get the list of distinct column names from the database is to use distinct() in conjunction with values().

In your case you can do the following to get the names of distinct categories:

q = ProductOrder.objects.values('Category').distinct()
print q.query # See for yourself.

# The query would look something like
# SELECT DISTINCT "app_productorder"."category" FROM "app_productorder"

There are a couple of things to remember here. First, this will return a ValuesQuerySet which behaves differently from a QuerySet. When you access say, the first element of q (above) you'll get a dictionary, NOT an instance of ProductOrder.

Second, it would be a good idea to read the warning note in the docs about using distinct(). The above example will work but all combinations of distinct() and values() may not.

PS: it is a good idea to use lower case names for fields in a model. In your case this would mean rewriting your model as shown below:

class ProductOrder(models.Model):
    product  = models.CharField(max_length=20, primary_key=True)
    category = models.CharField(max_length=30)
    rank = models.IntegerField()

@Jonathan Liuti 2012-10-16 15:03:18

The method described below is now available in django 1.4 and is nice if you need ProductOrder instance with field aware distinct ;-)

Related Questions

Sponsored Content

9 Answered Questions

[SOLVED] What is a "slug" in Django?

11 Answered Questions

[SOLVED] How to combine two or more querysets in a Django view?

15 Answered Questions

[SOLVED] differentiate null=True, blank=True in django

15 Answered Questions

[SOLVED] How do I do a not equal in Django queryset filtering?

20 Answered Questions

[SOLVED] LINQ's Distinct() on a particular property

3 Answered Questions

[SOLVED] Select distinct values from a table field

17 Answered Questions

1 Answered Questions

[SOLVED] Django query with distinct and order_by

1 Answered Questions

[SOLVED] Django do a simple group by / count statement

Sponsored Content