Question 90273 in PostgreSQL Question posted in
The official documentation can be found here.

Django combine queries with and (&) in m2m field – Postgresql

I have such models:


class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    tags = models.ManyToManyField(to="tag", related_name="tags", blank=True)

    def __str__(self):
        return self.title


class Tag(models.Model):
    value = models.CharField(max_length=50)
    parent = models.ManyToManyField("Tag", related_name="children", blank=True)
    image = models.ImageField(upload_to="tags", null=True, blank=True)
    
    def __str__(self):
        return self.value

And I have an object, for example, post C which has tag 2 and tag 3

Currently I cannot create a combined ( q1 & q2 ) query, where I will be able to filter by that condition.

I am using combined queries because the case is more complex(so double filter won’t do), I want to be able to filter by such queries as " 2 or ( 3 and 4 ) ", "(1 or 2) and (3 or 4)"

>>> q1
<Q: (AND: ('tags__in', '2'))>
>>> q2
<Q: (AND: ('tags__in', '3'))>
>>> Post.objects.filter(q1)
<QuerySet [<Post: post_B>, <Post: post C>, <Post: post D>]>
>>> Post.objects.filter(q2)
<QuerySet [<Post: post C>, <Post: post D>]>
>>> Post.objects.filter(q1 & q2)
<QuerySet []>
>>> str(Post.objects.filter(q1).query)
'SELECT "posts_post"."id", "posts_post"."title", "posts_post"."content" FROM "posts_post" INNER JOIN "posts_post_tags" ON ("posts_post"."id" = "posts_post_tags"."post_id") WHERE "posts_post_tags"."tag_id" IN (2)'
>>> str(Post.objects.filter(q2).query)
'SELECT "posts_post"."id", "posts_post"."title", "posts_post"."content" FROM "posts_post" INNER JOIN "posts_post_tags" ON ("posts_post"."id" = "posts_post_tags"."post_id") WHERE "posts_post_tags"."tag_id" IN (3)'
>>> str(Post.objects.filter(q1 & q2).query)
'SELECT "posts_post"."id", "posts_post"."title", "posts_post"."content" FROM "posts_post" INNER JOIN "posts_post_tags" ON ("posts_post"."id" = "posts_post_tags"."post_id") WHERE ("posts_post_tags"."tag_id" IN (2) AND "posts_post_tags"."tag_id" IN (3))'

What I’ve found is https://stackoverflow.com/a/8637972/20685072

It says " ANDed Q objects would not work "

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I resolved it using raw Sql queries, UNION and INTERSECT


  2. 0 
    from django.db.models import Q
query = Q()

    for the or condition //

    query.add(Q(id__in=[1,2] | Q(id__in=[3,4], query.connector)

    as you asked for 2 and 3 //

    query.add(Q(id=2) & Q(id=3), query.connector)


data = Model.objects.filter(query)

    depends what is the requirement you can modify it as per need

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search