Django: M2M field query using 'in' each value in a list

Question

I'm trying to find Problem objects having all the tags contained in tags related to it.

I have the following models.

class Tag(models.Model):
    name = models.CharField(verbose_name='Tag Name', max_length=50)
    description = models.CharField(verbose_name='Description', max_length=100, null=True, blank=True)

class Problem(models.Model):
    name = models.CharField(verbose_name='Problem Name', max_length=250)
    contest_info = models.ManyToManyField(ContestInfo, verbose_name='Contest Info')
    tags = models.ManyToManyField(Tag, verbose_name='Tags')

I've followed these posts: Post-1 and Post-2.
But, none worked.

My approaches.

Approach 1

query = [Q(tags__id__in=[tag]) for tag in tags]
problems = problems.filter(reduce(__and__, query))

Returns empty queryset.

Approach 2

for tag in tags:
    problems &= problems.filter(tags__id__in=[tag])

Returns result of OR instead of AND. Using intersection() also gives the same result.

I want to get those problems which have all the tags in the list. How to do this ?

N.B: problems is a queryset and tags is list of ids of Tag objects.

take a look at https://stackoverflow.com/questions/13270513/django-manytomany-filter-matching-on-all-items-in-a-list — itzMEonTV, Apr 20 '18 at 08:31

Basalex · Answer 1 · 2018-04-20T11:24:33.127

1

For postgresql what you can is this (tested):

from django.contrib.postgres.aggregates import JSONBAgg

problems = Problem.objects.annotate(tags_ids=JSONBAgg('tags__id'))
problems = problems.filter(tags_ids__contains=tags, tags_ids__contained_by=tags)

*where "tags is list of ids of Tag objects."

edited Apr 20 '18 at 11:24

answered Apr 20 '18 at 10:56

Basalex

1,147
9
20

Ok. I'll try this asap. Btw, `tags` and `Tag` model both already contains `unique` ids and objects respectively. So, do i still need to filter them by `DISTINCT` ?? – MD. Khairul Basar Apr 20 '18 at 11:15
1

I think you don't have to use DISTINCT,but I wasn't sure, that`s why I added it to the answer – Basalex Apr 20 '18 at 11:24
It's not working well. It's filtering out many objects. :( I have other filters too applied on `problems`, not sure if that's the reason. – MD. Khairul Basar Apr 20 '18 at 12:57
Are you sure that you used both 'contains' and 'contained_by' ? I`ve checked it for several times and it works just fine – Basalex Apr 20 '18 at 13:22
Yes, I did. It's filtering out some objects that contain all the `tags`. – MD. Khairul Basar Apr 20 '18 at 13:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/169446/discussion-between-md-khairul-basar-and-basalex). – MD. Khairul Basar Apr 20 '18 at 13:26

MD. Khairul Basar · Accepted Answer · 2018-04-21T05:10:37.990

0

I solved this problem following this answer. Thanks to @itzMEonTV.
But, it requires looping and I would like to achieve this without looping if possible.

Here's the solution.

for tag in tags:
    problems = problems.filter(tags__id=tag)

edited Apr 21 '18 at 05:10

answered Apr 20 '18 at 13:02

MD. Khairul Basar

4,976
14
41
59

Django: M2M field query using 'in' each value in a list

2 Answers2