Django Postgres ArrayField聚合和过滤 [英] Django Postgres ArrayField aggregation and filtering

问题描述

接着是这个问题： Django Postgresql ArrayField聚合

我有一个 ArrayField 的类别，并且我想检索它具有的所有唯一值-但是应该对结果进行过滤，以便仅以值开头

I have an ArrayField of Categories and I would like to retrieve all unique values it has - however the results should be filtered so that only values starting with the supplied string are returned.

这样做的最Django方法是什么？

What's the "most Django" way of doing this?

给出一个动物模型，如下所示：

Given an Animal model that looks like this:

class Animal(models.Model):
    # ...
    categories = ArrayField(
        models.CharField(max_length=255, blank=True),
        default=list,
    )
    # ...

然后按照另一个问题的答案，它适用于查找所有未过滤的类别。

Then, as per the other question's answer, this works for finding all categories, unfiltered.

all_categories = (
    Animal.objects
    .annotate(categories_element=Func(F('categories'), function='unnest'))
    .values_list('categories_element', flat=True)
    .distinct()
)

但是，现在，当我尝试过滤结果时，我失败了，不仅使用 __ startswith ，而且还使用所有类型的 filter ：

However, now, when I attempt to filter the result I get failure, not just with __startswith but all types of filter:

all_categories.filter(categories_element__startswith('ga'))
all_categories.filter(categories_element='dog')

stacktrace的底部是：

Bottom of stacktrace is:

DataError: malformed array literal: "dog"
...
DETAIL:  Array value must start with "{" or dimension information.

...，这似乎是因为Django尝试执行第二次 UNNEST -这是它生成的SQL：

... and it appears that it's because Django tries to do a second UNNEST - this is the SQL it generates:

...) WHERE unnest("animal"."categories") = dog::text[]

如果我在PSQL中编写查询，由于 UNNEST ，似乎需要子查询：

If I write the query in PSQL then it appears to require a subquery as a result of the UNNEST:

SELECT categories_element
FROM (
    SELECT UNNEST(animal.categories) as categories_element
) ul
WHERE ul.categories_element like 'Ga%';

是否可以通过Django ORM进行有效查询？还是我应该放弃ORM并使用原始SQL？

Is there a way to get Django ORM to make a working query? Or should I just give up on the ORM and use raw SQL?

推荐答案

您可能设计了错误的数据库。

You probably have the wrong database design.

提示：未设置数组；搜索特定的数组元素可能是数据库设计错误的标志。考虑使用一个单独的表，其中每个项都有
行，这将是一个数组元素。
会更容易搜索，并且可能会针对大量
元素进行更好地扩展。

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

http://www.postgresql.org/docs/9.1/static/arrays。 html

这篇关于Django Postgres ArrayField聚合和过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！