1

Given I have a Product model and a one-to-many ProductTag model.

class Product(models.Model):
    [...]


class ProductTag(models.Model):
    product = models.ForeignKey(Product)
    tag_value = models.CharField()
    [...]

If I have 3 products with some tags:

  1. ProductA [TagA]
  2. ProductB [TagB]
  3. ProductC [TagB/TagC]

I want to dynamically query "products" with some tags.

  1. This query returns just "ProductA" (as expected).
Product.objects.filter(Q(producttag__tag_value="TagA"))
  1. This query returns all 3 products (as expected).
Product.objects.filter(
    Q(producttag__tag_value="TagA") | Q(producttag__tag_value="TagB")
)
  1. I would expect the following query to return just "ProductC"
Product.objects.filter(
    Q(producttag__tag_value="TagB") & Q(producttag__tag_value="TagC")
)

But I get an empty queryset. Why does query #3 not work?

Using __in query also returns wrong results (as expected)

Product.objects.filter(producttag__tag_value__in=["TagB", "TagC"])

The above query returns both ProductB / ProductC.

UPDATE

The reason why I am doing lookups with Q objects is that the query is based on user input from an API endpoint. e.g

  • "tag=TagA"
  • "tag=TagA AND tag=TagB"
  • "tag=TagA AND NOT tag=TagC"

So I need to run a dynamic Django query based on the user input and not able to hardcode some query.

mishbah
  • 5,487
  • 5
  • 25
  • 35
  • Query #3 effectively asks for the `ProductTag.tag_value` to equal both `TagB` and `TagC` at the same time on a single row, and that is obviously impossible. I get what you are trying to achieve, but still thinking on the possible solution. It's a consequence of the SQL model, you are always filtering based on a single row of a table... – Alexandr Tatarinov Jul 03 '21 at 20:00

2 Answers2

0

Query #3 effectively asks for the ProductTag.tag_value to equal both TagB and TagC at the same time on a single row, and that is obviously impossible. It's a consequence of the SQL query syntax/model, you are always filtering based on a single row of a table.

I didn't come up with anything fancier and more performant than chaining EXISTS clauses (only works on Django 3.x):

rel_tag = ProductTag.objects.filter(product=OuterRef('pk'))
Product.objects.filter(
    Exists(rel_tag.filter(tag_value="TagB")) & 
    Exists(rel_tag.filter(tag_value="TagC"))
)
Alexandr Tatarinov
  • 3,946
  • 1
  • 15
  • 30
  • BTW I tried using `EXISTS` (using Django 2.2x) ``` tag_query = ProductTag.objects.filter(product=OuterRef('pk')) qs = Product.objects.annotate( matched=Exists(tag_query.filter(tag_value="TagA")) & Exists(tag_query.filter(tag_value="TagB")) ).filter(matched=True) ``` Django raises `NotImplementedError: Use .bitand() and .bitor() for bitwise logical operations.` – mishbah Jul 05 '21 at 12:18
  • Yeah, right, it was only fixed in the 3.x version. I'll amend a solution for older versions – Alexandr Tatarinov Jul 05 '21 at 12:29
  • TypeError: 'Exists' object is not iterable. – mishbah Jul 05 '21 at 12:46
  • Well, then you are doomed) The only way to go is via annotate, but you'll have to give it names, and it complicates the dynamic query building – Alexandr Tatarinov Jul 05 '21 at 13:01
0

The following query work:

qs1 = Product.objects.filter(Q(producttag__tag_value="TagB"))
qs2 = Product.objects.filter(Q(producttag__tag_value="TagC"))

queryset = qs1 & qs2

The above query returns just ProductC.

Roughly based on this answer https://stackoverflow.com/a/33271387/1682844

mishbah
  • 5,487
  • 5
  • 25
  • 35
  • Wouldn't recommend using this, it's SQL `INTERSECTION` which is pretty poorly supported by Django (same for `UNION`), you may be unable to later make some of the modifications to the query. Please see my answer for the alternative – Alexandr Tatarinov Jul 03 '21 at 20:15
  • From the docs: In addition, only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), exists(), order_by(), and values()/values_list()) are allowed on the resulting QuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the combined queries. – Alexandr Tatarinov Jul 03 '21 at 20:17
  • Updated my question with more details. Query is dynamic based on user input and will be difficult to use things like "Exists". – mishbah Jul 03 '21 at 20:29
  • Well, nothing stops you from generating Exists / ~Exists queries the same way as you would generate `Q(producttag__tag_value="TagB") & Q(producttag__tag_value="TagC")` queries – Alexandr Tatarinov Jul 03 '21 at 20:53
  • Tell me if you need some guidance on how to achieve that – Alexandr Tatarinov Jul 03 '21 at 22:07