Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
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()
                The method described below is now available in django 1.4 and is nice if you need ProductOrder instance with field aware distinct ;-)
– Jonathan Liuti
                Oct 16, 2012 at 15:03
                I want to know how to modify this code so that it will work on where statements as well. Like select distinct(app_productorder.category) from app_productorder where app_productorder.rank <30; ?
– Prakash Dahal
                Apr 30, 2021 at 14:20
It's quite simple actually if you're using PostgreSQL, just use distinct(columns) (documentation).
Productorder.objects.all().distinct('category')
Note that this feature has been included in Django since 1.4
                @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
– Wolph
                Oct 4, 2010 at 7:35
                Note: unless you're using PostgreSQL, you can't give distinct() an argument.  Best stick with the accepted solution above.
– Mark Chackerian
                Feb 4, 2013 at 21:04
                Underrated answer! Would have been better if you have explained it better. I was facing a issue related to ordering.
– Shahriar Rahman Zahin
                Jul 3, 2022 at 6:26
                I'm glad i stumbled over this answer, which worked, but why is this the case? I don't understand why ordering it makes a difference.
– Steven Gillies
                Sep 8, 2022 at 16:51
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'}
>>> 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.
Update:
This is answer is great for making queries in the Django shell during development.  DO NOT use this solution in production unless you are absolutely certain that you will always have a trivially small number of results before set is applied.  Otherwise, it's a terrible idea from a performance standpoint.
                values_list does not put DISTINCT in the sql query, so this would bring multiple values if there were.
– mehmet
                Jun 29, 2016 at 22:10
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.