从表字段中选择不同的值

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:

我在纠结我的脑袋是不是要绕着那个叫Django的怪物转。我想要做的是获得不同值的列表字段在表内....相当于下列其中一项:

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:

在使用原始sql之前，我至少希望使用Django的方式。例如，有一个表:

id, street, city

id、街道、城市

1, Main Street, Hull

1、主要街道,船体

2, Other Street, Hull

2、其他街道,船体

3, Bibble Way, Leicester

3,Bibble方式,莱斯特

4, Another Way, Leicester

4,另一种方式,莱斯特

5, High Street, Londidium

5、高街Londidium

I'd like to get:

我想买:

Hull, Leicester, Londidium.

船体,Londidium莱斯特。

2 个解决方案

#1

141

Say your model is 'Shop'

假设你的模特是" 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()

由于您可能拥有元类排序属性集，所以您可以使用order_by()而无需参数来清除使用distinct()时的排序。参见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.

如果您不希望对查询应用任何排序，甚至不希望对默认排序应用任何排序，那么可以在没有参数的情况下调用order_by()。

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()  # returns a dictionary

或

models.Shop.objects.order_by().values_list('city').distinct()  # returns a list of tuples.

You can also add flat=True to values_list to have a flat list.

您还可以向values_list添加flat=True以获得一个flat list。

参见:获取按字段划分的Queryset的不同值

#2

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!

除了jujujujule仍然是非常相关的答案之外，我发现还需要注意order_by()对不同的(“field_name”)查询的含义。然而，这是Postgres唯一的特性!

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

如果您正在使用Postgres，并且定义了一个查询应该不同的字段名，那么order_by()需要以相同序列中的相同字段名(或字段名)开头(以后可能会有更多字段)。

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.

当指定字段名时，必须在QuerySet中提供order_by()， order_by()中的字段必须以不同的()字段为起点，顺序相同。

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.

例如，SELECT DISTINCT ON (a)给出了a列中每个值的第一行。

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:

如果你想从e-g中提取你所熟悉的城市的列表，那么jujule的例子就必须适合这个:

# returns an iterable Queryset of cities.
models.Shop.objects.order_by('city').values_list('city', flat=True).distinct('city')

#1

141