Django .exclude() returns empty queryset-CodePudding

I have a problem with .exclude() when making a QuerySet.

My models involved are:

Profession

class Profession(models.Model):
    profession_name = models.CharField(max_length=20)
    equipment = models.ManyToManyField(Equipment, blank=True)
    ability = models.ManyToManyField(Ability, blank=True)
    skill = models.ManyToManyField(Skill, blank=True)
    skill_advanced = models.ManyToManyField(SkillAdvanced, blank=True)

Ability

class Ability(models.Model):
    ability_name = models.CharField(max_length=35)

When I create QuerySet with:

self.prof_abilities = Profession.objects.filter(profession_name="Acolyte").values('ability')

I get:

<QuerySet [{'ability': 2}, {'ability': 69}, {'ability': 81}, {'ability': 86}, {'ability': 23}]>

But I would like to exclude some values, so I use this instead:

self.prof_abilities = Profession.objects.filter(profession_name="Acolyte").exclude(ability__in=[2, 23, 81, 86]).values('ability')

But the outcome is an empty QuerySet: <QuerySet []>.
I've tried various tweeks like .exclude(ability__id__in=[2, 23, 81, 86]) or .exclude(ability__ability_name__in=['Foo name', 'Bar name'] but with no success.

I'm new to Django so if this is something obvious, I would also appreciate some pointers where to read more on my mistake.

CodePudding user response：

Your queryset is for the Profession model, hence when you write queryset.exclude(ability__in=<list_of_abilities>) you get a queryset where you exclude Profession (Not Ability) instances where the ability is in the given list, i.e. if any of the Ability related to a Profession is in that list that profession would be excluded. Instead if you get the Profession object first and then filter on the relation manager you will get your expected result:

queryset = Profession.objects.filter(profession_name="Acolyte")

for profession in queryset:
    print(profession.ability.exclude(id__in=[2, 23, 81, 86]))

CodePudding user response：

Since Ability is a manyToManyField, the each profession.ability object will have django.db.models.fields.related.ManyRelatedManager and not the direct list of ability objects. So if you want to get All professions that excludes ability with specific ids you can try changing your query from

self.prof_abilities = Profession.objects.filter(profession_name="Acolyte").exclude(ability__in=[2, 23, 81, 86]).values('ability')

self.prof_abilities = Profession.objects.filter(profession_name="Acolyte").exclude(ability__in=Ability.objects.filter(id__in=[2, 23, 81, 86])).values('ability')

filter and exclude behaves differently with respect to multi-valued relationships.

Although didn't find a link for valid explanation for my answer, here is a link from documentation discussing something related

Scroll down to note part of this section link: https://docs.djangoproject.com/en/3.2/topics/db/queries/#spanning-multi-valued-relationships