英文:
Django Many2Many query to find all `things` in a group of `categories`
问题
Here's the translation of the provided content without code:
给定这些Django模型：
-
Thing（物品）模型：
- 属性：name（名称）
-
Category（类别）模型：
- 属性：name（名称）
- 属性：things（与Thing模型的多对多关系，关联名称为“categories”）
如果要查找所有位于一组给定类别中的所有物品，可以使用以下方法：
假设有5个类别，其ID为1、2、3、4、5。
并且假设有一组类别子集：
my_cats = Category.objects.filter(id__in=[2,3])
要查找位于类别2和3中的所有物品，可以使用以下查询：
# 查找位于类别2或3中的所有物品things = Thing.objects.filter(categories__in=[2, 3])
上述查询将返回位于类别2或3中的所有物品，但不会限制为同时位于两个类别中的物品。
要查找同时位于类别2和3中的物品，可以尝试以下查询：
# 查找同时位于类别2和3中的物品things = Thing.objects.filter(categories=2).filter(categories=3)
或者，您可以使用Q对象来实现相同的目标：
from django.db.models import Q# 使用Q对象查找同时位于类别2和3中的物品things = Thing.objects.filter(Q(categories=2) & Q(categories=3))
上述查询将返回位于类别2和3中的物品。但是，请确保使用.distinct()来消除重复项。
希望这可以帮助您解决问题！如果需要更多帮助，请随时提问。
英文:
Given these Django models:
from django.db import modelsclass Thing(models.model):name = models.CharField('Name of the Thing')class Category(models.model):name = models.CharField('Name of the Category')things = models.ManyToManyField(Thing, verbose_name='Things', related_name='categories')
Note that all the categories a Thing is in can be found by:
thing = Thing.objects.get(id=1) # for examplecats = thing.categories.all() # A QuerySet
I'm really struggling to build a query set that returns all Things in all of a given set of Categories.
Let's say we have 5 categories, with IDs 1, 2, 3, 4, 5.
And say I have a subset of categories:
my_cats = Category.objects.filter(id__in=[2,3])
I want to find all Things that are in say categories, 2 AND 3.
I can find all Things in category 2 OR 3 easily enough. For example this:
Thing.objects.filter(categories__in=[2,3])
seems to return just that, Things in category 2 OR 3.
And something like:
Thing.objects.filter(Q(categories=2)|Q(categories=3))
also, but this returns nothing:
Thing.objects.filter(Q(categories=2)&Q(categories=3))
I might envisage something like:
Thing.objects.filter(categories__contains=[2,3])
but of course that's a dream as contains operates on strings not ManyToMany sets.
Is there a standard trick here I'm missing?
I spun up a sandbox here to test and demonstrate:
https://codesandbox.io/p/sandbox/django-m2m-test-cizmud
It implements this simple pair of models and populates the database with a small set of things and categories and tests the queries, here's the latest state of it:
print("Database contains:")for thing in Thing.objects.all():print(f"\t{thing.name} in categorties {[c.id for c in thing.categories.all()]}")print()# This works fine. Prints:# Cat1 OR Cat2: ['Thing 1', 'Thing 5', 'Thing 4']things = Thing.objects.filter(Q(categories=1) | Q(categories=2)).distinct()print(f"Cat1 OR Cat2: {[t.name for t in things]}")# We would love this to return Thing4 and thing5# The two things in the test data set that are in# Category 2 and in Category 3.# But this does not work. It prints:# Cat2 AND Cat3: []# because# What does yield ['Thing 4', 'Thing 5']?print("\nAiming to to get: ['Thing 4', 'Thing 5']")things = Thing.objects.filter(Q(categories=2) & Q(categories=3)).distinct()print(f"Try 1: Cat2 AND Cat3: {[t.name for t in things]}")# This also fails, producing an OR not ANDthings = Thing.objects.filter(categories__in=[2, 3]).distinct()print(f"Try 2: Cat2 AND Cat3: {[t.name for t in things]}")# Also failsthings = Thing.objects.filter(categories__in=[2, 3])\.filter(categories=2).distinct()print(f"Try 3: Cat2 AND Cat3: {[t.name for t in things]}")# Also failsthings = Thing.objects.filter(categories__in=[2, 3], categories=2)\.distinct()print(f"Try 4: Cat2 AND Cat3: {[t.name for t in things]}")
and it's output:
Database contains:Thing 1 in categorties [1, 2]Thing 2 in categorties [3, 4]Thing 3 in categorties [5]Thing 4 in categorties [2, 3]Thing 5 in categorties [1, 2, 3]Cat1 OR Cat2: ['Thing 1', 'Thing 5', 'Thing 4']Aiming to to get: ['Thing 4', 'Thing 5']Try 1: Cat2 AND Cat3: []Try 2: Cat2 AND Cat3: ['Thing 1', 'Thing 4', 'Thing 5', 'Thing 2']Try 3: Cat2 AND Cat3: ['Thing 1', 'Thing 4', 'Thing 5']Try 4: Cat2 AND Cat3: ['Thing 1', 'Thing 4', 'Thing 5']
I guess if I can work it out in SQL, we can write us a custom lookup:
https://docs.djangoproject.com/en/4.2/howto/custom-lookups/
But why do I think this must already have been written? How this be such a unique and new use case?
答案1
得分: 1
Sure, here's the translated code part:
Thing.objects.annotate(cat_count=Count('id', filter=Q(categories__in=[2, 3]))).\filter(cat_count__gte=2).values('id', 'cat_count')
The comment at the end remains in English.
英文:
Thing.objects.annotate(cat_count=Count('id', filter=Q(categories__in=[2, 3]))).\filter(cat_count__gte=2).values('id', 'cat_count')
Try it. cat_count__gte=2 because the two numbers are 2, 3.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论