CODEWITHSUNDEEP
djangodjango-modelsmany-to-many
Rob
Rob

Reputation: 703

Django How To Query ManyToMany Relationship Where All Objects Match

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.

Upvotes: 2

Views: 2028

Answers (4)

Celmar Y.
Celmar Y.

Reputation: 321

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)

Upvotes: 2

Chandra Shekhar Pandey
Chandra Shekhar Pandey

Reputation: 157

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

Upvotes: 1

Rob
Rob

Reputation: 703

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

Upvotes: 2

schillingt
schillingt

Reputation: 13731

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.

Upvotes: 1

Related Questions