Django How To Query ManyToMany Relationship Where All Objects Match

Question

I have the following models:

## Tags for issues
class issueTags(models.Model):
    name = models.CharField(max_length=400)
class issues(models.Model):
    tags = models.ManyToManyField(issueTags,blank = True)

In my view I get an array from some client side JavaScript i.e.

(Pdb) array_data = request.POST['arr']
(Pdb) array_data
'["2","3"]'

How should I filter my issues object to find all issues which match all tags in the array? (the 2,3 are the ID values for tag__id.

If there is a better way to arrange the objects that would also work so I can search in this fashion.

score 2 · Accepted Answer · answered Nov 21 '18 at 10:46

It isn't most elegant solution or pythonic but I ended up just looping around the resulting filter.

def filter_on_category(issue_object,array_of_tags):
    #keep filtering to make an and
    i = 0
    current_filter = issue_object
    while (i < (len(array_of_tags))):
        #lets filter again
        current_filter=current_filter.filter(tags__id__in=array_of_tags[i])
        i=i+1

    return current_filter

Celmar Y. · Answer 2 · 2021-03-07T08:39:59.573

At the time of writing this, the existing answers are either incorrect (e.g. filtering matching all Issues that have any of the specified tags and the correct tag count) or inefficient (e.g. attaching filters in a loop).

For the following models:

class IssueTag(models.Model):
    name = models.CharField(max_length=400, blank=True)

class Issue(models.Model):
    label = models.CharField(max_length=50, blank=True)
    tags = models.ManyToManyField(IssueTag, related_name='issues')

I suggest using Django Annotation in conjunction with a filter like so:

from django.db.models import Count, Q

tags_to_match = ['tag1', 'tag2']

issues_containing_all_tags = Issue.objects \
    .annotate(num_correct_tags=Count('tags',
                                     filter=Q(tags__name__in=tags_to_match))) \
    .filter(num_correct_tags=2)

to get all Issues that have all required tags (but may have additional tags, as is required in the question).

This will produce the following SQL query, that resolves all tag matching in a single IN clause:

SELECT "my_app_issue"."id", "my_app_issue"."label", 
    COUNT("my_app_issue_tags"."issuetag_id") 
        FILTER (WHERE "my_app_issuetag"."name" IN ('tag1', 'tag2'))
        AS "num_correct_tags"
FROM "my_app_issue"
LEFT OUTER JOIN "my_app_issue_tags" ON ("my_app_issue"."id" = "my_app_issue_tags"."issue_id")
LEFT OUTER JOIN "my_app_issuetag" ON ("my_app_issue_tags"."issuetag_id" = "my_app_issuetag"."id")
GROUP BY "my_app_issue"."id", "my_app_issue"."label"
HAVING COUNT("my_app_issue_tags"."issuetag_id")
FILTER (WHERE ("my_app_issuetag"."name" IN ('tag1', 'tag2'))) = 2; 
args=('tag1', 'tag2', 'tag1', 'tag2', 2)

score 1 · Answer 3 · answered Nov 20 '18 at 15:54

1

I haven't tested this, but I think you could do the following:

from django.db.models import Q

array_data = array_data.split(',')
issues.objects.filter(
    tags__in=array_data,
).exclude(
    # Exclude any that aren't in array_data
    ~Q(tags__in=array_data)
).annotate(
    matches=Count(tags, distinct=True)
).filter(
    # Make sure the number found is right.
    matches=len(array_data)
)

FYI, you should be using Issue, IssueTag for your model names to follow Django's naming pattern.

answered Nov 20 '18 at 15:54

schillingt

13,493
2
32
34

Thanks for the answer, the issue I had with it was that it was still an or style statement with the tags__in pulling in any issues which had just one of the tags and the exclude didnt seem to throw out the ones which didnt have all of the tags. It might just have been that my test scenario was flawed though. Thanks for the comment on case too :) – Rob Nov 21 '18 at 10:44

score 1 · Answer 4 · answered Aug 15 '20 at 14:32

Django field lookups argument (__) for many-to-many fields needs list argument. I have created a dummy list for each array element of IssueTags and pass it to lookups argument and it works as expected.

Let you have this models:

class IssueTags(models.Model):
    name = models.CharField(max_length=400)

class Issues(models.Model):
    tags = models.ManyToManyField(IssueTags,blank = True)

You want to get Issues which contains all of these IssueTags = ["1","2","3"]

issue_tags_array = ["1","2","3"]

#First initialize queryset 
queryset = Issues.objects.all() 
i = 0 
while i < len(issue_tags_array): 
  #dummy issue_tag list
  issue_tag = [issue_tags_array[i]]
  #lets filter again 
  queryset = queryset.filter(tags__id__in=issue_tag) 
  i=i+1
return queryset

Django How To Query ManyToMany Relationship Where All Objects Match

4 Answers4