筛选两个查询集，两者之间具有多对多关系

Question

我有3种不同的模型，活动，类别和部门。类别和活动之间存在多对多关系，类别与部门之间存在一对多关系(类别与一个部门(。

在表格中，我正在获取部门(必填(，我还可以获得类别(非必填字段(。我将其存储到会话中，然后我想选择所选类别(如果有选择(或部门中的每个活动。

选择一个类别后，我只需在activities_have_category上过滤活动即可。但是对于只有扇区的情况，我无法正确过滤它。

我尝试了一个非常相似的问题的解决方案，但它不起作用......

Solution tried: activity.objects.filter(category__sector__sector_name =request.session['sector']['sector_name'])
Result: Cannot resolve keyword 'category' into field. Choices are: activities_have_category, activities_have_countries, activities_have_output_outcome_impact, activity_name, description, outcome_impact, output_outcome, product_service
What I would like to get in SQL:
SELECT  activity_name FROM activity WHERE activities_have_category IN (SELECT category_name FROM category WHERE category_sector = "sector selected")
class sector(models.Model):
    sector_name = models.CharField(max_length=255, primary_key=True)
    description = models.TextField()
    def __str__(self):
        return self.sector_name
class category(models.Model):
    category_name = models.CharField(max_length=255, primary_key=True)
    description = models.TextField()
    category_sector = models.ManyToManyField('sector')
    def __str__(self):
        return self.category_name

class activity(models.Model):
    activity_name = models.CharField(max_length=255, primary_key=True)
    description = models.TextField()
    product_service = models.TextField()
    output_outcome = models.TextField()
    outcome_impact = models.TextField()
    activities_have_category = models.ManyToManyField('category')
    activities_have_output_outcome_impact = models.ManyToManyField('output_outcome_impact')
    activities_have_countries = models.ManyToManyField('country')
    def __str__(self):
        return self.activity_name

感谢您的帮助。

Answer 1

关系的名称是activities_have_category，此外，category与sector的关系是category_sector，所以你应该像这样查询：

activity.objects.filter(
    activities_have_category__category_sector__sector_name=request.session['sector']['sector_name']
)

给定还可以提供类别值，您可以像以下方式进行筛选：

activity.objects.filter(
    activities_have_category__category_sector__sector_name=request.session['sector']['sector_name'],
    activities_have_category__category_name=category_name
)

但是，我强烈建议使用较短的名称。通常不会在名称前面加上模型的名称，根据PEP-8，您应该对模型使用CamelCase，您可能还想在这里创建一个抽象类来提取公共字段，例如：

class NameDescModel(models.Model):
    name = models.CharField(max_length=255, primary_key=True)
    description = models.TextField()
    class Meta:
        abstract = True
    def __str__(self):
        return self.name
class Sector(NameDescModel):
    pass

class Category(NameDescModel):
    sectors = models.ManyToManyField('Sector')

class Activity(NameDescModel):
    product_service = models.TextField()
    output_outcome = models.TextField()
    outcome_impact = models.TextField()
    categories = models.ManyToManyField('Category')
    output_outcome_impacts = models.ManyToManyField('output_outcome_impact')
    countries = models.ManyToManyField('Country')

现在，查询将如下所示：

Activity.objects.filter(
    categories__sectors__name=request.session['sector']['sector_name'],
    category__name=category_name
)