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()
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
can_distinct_on_fields
which appears to be Postgres-only
all()
is not necessary here
User order by with that field, and then do distinct.
ProductOrder.objects.order_by('category').values_list('category', flat=True).distinct()
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.
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.
Success story sharing
where
statements as well. Likeselect distinct(app_productorder.category) from app_productorder where app_productorder.rank <30;
?select distinct on
in sql: geekytidbits.com/postgres-distinct-on