在 Django 中选择 DISTINCT 单个列? [英] Select DISTINCT individual columns in django?

问题描述

我很好奇是否有任何方法可以在 Django 中进行查询，而不是下面的SELECT * FROM...".我正在尝试执行SELECT DISTINCT columnName FROM ...".

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

其中 Rank 是 Category 中的一个等级.我希望能够遍历所有类别，对该类别中的每个等级进行一些操作.

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.

我宁愿避免使用原始 SQL，但如果我必须去那里，那也没关系.虽然我以前从未在 Django/Python 中编写过原始 SQL.

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.

推荐答案

从数据库中获取不同列名列表的一种方法是使用 distinct() 结合 values().

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"

这里有几件事要记住.首先，这将返回一个 ValuesQuerySet，它的行为与 QuerySet 不同.当您访问 q(上面)的第一个元素时，您将获得一个 dictionary，而不是 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.

其次，最好阅读警告说明 在有关使用 distinct() 的文档中.上面的例子会起作用，但 distinct() 和 values() 的所有组合可能都不起作用.

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:对模型中的字段使用小写名称是个好主意.在您的情况下，这意味着如下所示重写您的模型:

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

这篇关于在 Django 中选择 DISTINCT 单个列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！