A complex Django QuerySet query using ForeignKey

Question

I'm using Django. I created 3 models: Category, Feature, Question.

class Category(models.Model):
    category_name = models.CharField(max_length=300)
    category_order = IntegerRangeField(default=0, min_value=0)
    category_visible = models.BooleanField(default=True)

    def __str__(self):
        return self.category_name

    class Meta(object):
        ordering = ['category_order']

class Feature(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    feature_name = models.CharField(max_length=300)
    feature_code = models.CharField(max_length=50, unique=True)
    feature_predictable = models.BooleanField(default=False)

    def __str__(self):
        return self.feature_name

class Question(models.Model):
    feature = models.ForeignKey(Feature, on_delete=models.CASCADE, unique=True)
    question_order = IntegerRangeField(default=0, min_value=0)
    question_text = models.CharField(max_length=300)
    question_visible = models.BooleanField(default=True)

    def __str__(self):
        return self.question_text

    class Meta(object):
        ordering = ['question_order']

I want to write a function that will return me a list of categories with category_visible == True and the value of feature_predictable for at least one instance of the Feature model in that category is True, and for at least one Question question_visible == True.

Currently, I managed to create something like this:

category_queryset = category_queryset.filter(category_visible=True)

category_ids = []
for category in category_queryset:
    for feature in category.feature_set.all():
        if feature.feature_predictable is not True:
            continue
        for question in feature.question_set.all():
            if question.question_visible is not True:
                continue
            if category.id not in category_ids:
                category_ids.append(category.id)

return Category.objects.all().filter(id__in=category_ids)

But this is probably not the most efficient way. How to write it better? Thank you in advance for your help.

user_67579 · Answer

You can filter with

# visible question is for a predictable feature

Category.objects.filter(
    feature__feature_predictable=True,
    feature__question__question_visible=True
).distinct()

Here we thus retrieve Category objects for which there is a Feature where feature_predictable=True, and for these Features (with feature_predictable=True), there should also at least be one Question with question_visible=True.

In case we look for a Category with a feature that is predictable, and a Question that is visible, but that should not per se be for a Feature that is predictable, we can work with:

# visible question is not per se for a predictable feature

Category.objects.filter(
    feature__feature_predictable=True
).filter(
    feature__question__question_visible=True
).distinct()

A complex Django QuerySet query using ForeignKey

1 Answers