将Django RawQuerySet转换为Queryset [英] Convert django RawQuerySet to Queryset

问题描述

我有2个Django模型， ModelA 带有 ArrayField ，用于存储大量主键值（可能超过50k）

I have 2 Django models, ModelA with an ArrayField that is used to store a large list of primary key values (possibly 50k+ list)

class ModelA(models.Model):
    pk_values = ArrayField(models.IntegerField())

class CustomManager(manager.Manager):

    def get_for_index(self, index_id):
        qs = self.get_queryset()
        obj = ModelA.objects.get(pk=index_id)
        return qs.filter(id__in=obj.pk_values)

class ModelB(models.Model):
    # [...] some fields

    objects = CustomManager()

这有效：

qs = ModelB.objects.get_for_index(index_id=1)

但是，这在 pk_values很大的情况下会非常慢。

However, this would be super slow where "pk_values" is a large list.

所以我尝试做原始SQL查询：

So I tried doing raw SQL queries:

class CustomManager(manager.Manager):
    def get_for_index(self, index_id):
        qs = self.get_queryset()
        sql = "SELECT * FROM myapp_model_b JOIN myapp_model_a ON myapp_model_b.id = ANY(myapp_model_a.pk_values) WHERE myapp_model_a.id = '%s'" % index_id
        return qs.raw(sql)

但这返回 django.db。 models.query.RawQuerySet 实例。

但是，与此类似，我无法执行 queryset.values（）之后。

But with this, I cant do things like queryset.values() afterwards.

如何将其转换为普通的Django查询集？

How can I convert this to a normal Django queryset?

是否有更好的方法？

文档：

• ArrayField https://docs.djangoproject.com/zh-CN/2.0/ref/contrib/postgres/fields/#arrayfield


• 客户经理 https://docs.djangoproject.com/en/2.0/topics / db / managers /＃custom-managers-and-model-inheritance


• 原始查询 https://docs.djangoproject.com/en/2.0/topics/db/sql/#performing-raw-sql-查询

• ArrayField https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#arrayfield
• Custom Manager https://docs.djangoproject.com/en/2.0/topics/db/managers/#custom-managers-and-model-inheritance
• Raw queries https://docs.djangoproject.com/en/2.0/topics/db/sql/#performing-raw-sql-queries

推荐答案

您可以使用 Ra wSQL 表达式：

ModelB.objects.filter(id__in=RawSQL(
    'SELECT unnest(a.pk_values) FROM app_modela a WHERE a.id = %s',
    [index_id]
))

或者，您可以使用 extra（）：

ModelB.objects.extra(
    tables=['foo_modela'],
    where=[
        '"app_modelb"."id" = ANY("app_modela"."pk_values")',
        '"app_modela"."id" = %s',
    ],
    params=[index_id],
)

这篇关于将Django RawQuerySet转换为Queryset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！