Django相当于SqlAlchemy`any`来过滤`WHERE EXISTS`

I have two models, Sample and Run. A Sample can belong to multiple Runs. The Run model has name that I would like to use to filter Samples on; I would like to find all Samples that have a run with a given name filter. In SqlAlchemy, I write this like:

我有两个模型,Sample和Run。样本可以属于多个运行。 Run模型具有我想用来过滤Samples的名称;我想找到所有具有给定名称过滤器的运行的样本。在SqlAlchemy中,我这样写:

Sample.query.filter(Sample.runs.any(Run.name.like('%test%'))).all()

In Django, I start with:

在Django,我开始:

Sample.objects.filter(run__in=Run.objects.filter(name__icontains='test'))

Sample.objects.filter(run__name__icontains='test')

However both of these produce duplicates so I must add .distinct() to the end.

但是这两个都产生重复,所以我必须添加.distinct()到最后。

The Django approach of using distinct has terrible performance when there are a large number of predicates (because the distinct operation must runs over a large number of possible rows) whereas the SqlAlchemy runs fine. The repeated rows come from repeated left outer join from each predicate.

当存在大量谓词时,Django使用distinct的方法具有可怕的性能(因为不同的操作必须在大量可能的行上运行),而SqlAlchemy运行正常。重复的行来自每个谓词的重复左外连接。

For example:

Sample.objects.filter(Q(**{'run__name__icontains': 'alex'}) |
     Q(**{'run__name__icontains': 'baz'}) | ...)

EDIT: To make this a little more complicated, I do want the ability to have filters like:

编辑:为了使这更复杂一点,我希望能够有这样的过滤器:

(Q(**{'run__name__icontains': 'alex'}) | Q(**{'name__icontains': 'alex'})
  & Q(**{'run__name__icontains': 'baz'}) | Q(**{'name__icontains': 'baz'}))

which has a SQLAlchemy query like:

它有一个SQLAlchemy查询,如:

clause1 = Sample.runs.any(Run.name.like('%alex%')) | Sample.name.like('%test%')
clause2 = Sample.runs.any(Run.name.like('%baz%')) | Sample.name.like('%baz%')
Sample.query.filter(clause1 & clause2)

1 个解决方案

#1

Assuming this is your models.py:

假设这是你的models.py:

from django.db import models

class Sample(models.Model):
    name = models.CharField(max_length=255)

class Run(models.Model):
    name = models.CharField(max_length=255)
    sample = models.ForeignKey(Sample)

Since I wasn't able to figure out how to do this without using "distinct", or without using "raw" (in which, if you're forming your own SQL code, and can't rely on the ORM, then what's the point :p), I recommend to try replacing the Django ORM with SQLAlchemy, or use them along-side each other, since theoretically that would work. Sorry I couldn't be of much help :(

因为我不知道如何不使用“distinct”,或者不使用“raw”(其中,如果你正在构建自己的SQL代码,并且不能依赖ORM,那么我的要点:p),我建议尝试用SQLAlchemy替换Django ORM,或者将它们彼此并排使用,因为从理论上讲它可以工作。对不起,我帮助不大:(

Here is a fairly-recent blog post that can help you do that: http://rodic.fr/blog/sqlalchemy-django/

这是一篇相当新的博客文章,可以帮助你做到这一点:http://rodic.fr/blog/sqlalchemy-django/

#1