通过多个FK属性进行查询集过滤 [英] Queryset filtering by multiple FK attributes

问题描述

我的情况：

我有Django模型 HOST 和模型 PACKAGE （带有名称和版本），其中在主机模型上具有FK。现在，我需要过滤所有具有包且具有特定名称和特定版本的主机。因此类似于 Host.objects.filter（name = best_package，version__in = ['1.0'，'2.0']）
这一切都很好，也很容易，但是我需要对几个软件包重复此操作，这样我将获得在一个版本中具有每个希望软件包的主机。

I have django model HOST and model PACKAGE (with name and version) which has FK on host model. Now I need to filter all hosts which have packages with certain name and certain version.. therefore something like Host.objects.filter(name="best_package", version__in=['1.0','2.0']) This is all nice and easy, but I need to repeat this action for several packages so I would get host which has each of wished packages in one of the versions..

我尝试了两种方法，但均失败了，首先是在for循环中应用上面的 filter ，这很丑陋，但工作起来很开心，我发现这不是一个稳定的解决方案，我的一些查询将在接下来的 Sometimes 失败..是的，有时候！正如我自言自语的那样，我并没有深入研究Django ORM魔术，而是尝试用Q构建查询。我最终得到了以下代码

I tried two approached but both failed, first was applying filter above in the for loop, it was ugly but worked and as happy as I was I found out this isn't stable solution and some of my queries which I apply next SOMETIMES fails.. yes sometimes! As I said to my self I'm not going deeper in that hole of Django ORM magic I tried to build the query with Q. I ended up with following code

pckgs_query = reduce(
    operator.and_,
    (
        Q(packages__name=name, packages__version__in=versions)
        for name, versions in pckgs_dict.items()
    )
 )
hosts = Host.objects.filter(pckgs_query)

，但是不幸的是，这不能正常工作，因为我检查了它生成的SQL查询，我确定它正在寻找带有所有这些参数的单个PACKAGE对象，这些参数当然是不存在的...是否有人足够熟练地帮我忙吗？真的在这里迷路了，有点希望我的应用现在不在django中。

but unfortunately this isn't working properly, as I checked the SQL query it generates I'm sure it is looking for single PACKAGE object with all those parameters which of course does not exists... Is there somebody skilled enough to give me a hand? Really lost here and kind of wishing my app wasn't in django right now tbh

谢谢！

推荐答案

之所以不起作用，是因为您每次都限制相同的程序包。因此，如果键是'best_package'和'other_package'，则表示应该有一个包含作为名称都'best_package'和'other_package'

The reason this does not work is because you each time restrict the same package. If the keys are thus 'best_package' and 'other_package', you say that there should be a related package that has as name both 'best_package' and 'other_package', but since the package has only one name, that will of course fail.

我们可以用另一种方法解决此问题：首先过滤软件包以检索所有软件包与字典中的条目匹配，然后计数，从而检查相关包的数量是否与字典中的元素数量相同：

We can solve this in another way: we first filter the packages to retrieve all packages that match with an entry in the dictionary, and then count these, and thus check if the number of related packages is the same as the number of elements in the dictionary:

from django.db.models import Count, Q

pckgs_query = Q(
    *[Q(package_set__name=name, package_set__version__in=versions)
      for name, versions in pckgs_dict.items()],
    _connector=Q.OR
)

hosts = Host.objects.filter(pckgs_query).annotate(
    num_packages=Count('package_set')
).filter(
    num_packages=len(pckgs_dict)
)

因此，这只会返回 Host 已安装 all 个软件包，因为如果缺少软件包，则 num_packages 将小于 len（pckgs_dict）。

This will thus only return the Hosts that have all packages installed, since if a package is missing, then the num_packages will be less than len(pckgs_dict).

这篇关于通过多个FK属性进行查询集过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！