Multiplied db_results when multiple IN

Question

I am stuck since yesterday on a problem that is making me crazy, I feel like I am missing something. Here is the context: I am products (a node). In each products, I have tags (taxonomy terms), in a term reference field with an unlimited values possible.

I am simply trying to query my items, using a tag filter. But as soon as I select multiple tags, if the multiple as those tags, results is multiply by the number of corresponding tags.

The query is pretty big so I am simplifying it here to achieve the same result.

Node structure:

Title ------- | Quantity | Tags Tid
Product 1 --- | -- 1 --- | 12, 15
Product 2 --- | -- 4 --- | 13

Tables looks like this:

Node:

Title ------- | Nid
Product 1 --- | 320
Product 2 --- | 321

Quantity:

quantity_value | entity_id
1 ------------ | 320
4 ------------ | 321

Tags:

tid | entity_id
12  | 320
13  | 321
15  | 320

I am using db_query for my query:

So let's say that I want to filter on product that has Tags tids equal to 12 and 15 (product 1)

$query = db_query("
  SELECT n.title, SUM(q.quantity_value)
  FROM {node} n
  JOIN {quantity} q
  ON q.entity_id = n.nid
  LEFT JOIN {tags} t
  ON t.entity_id = n.nid
  WHERE t.tid IN (12, 15)
  GROUP BY n.title
  HAVING COUNT(DISTINCT t.tid) = count(12,15)
");
$results = $query->fetchAll();

That query returns me 1 row, with product 1, but quantity = 2, instead of 1. I have tried sub querying the "IN" and multiple other stuff, but nothing has worked. The only solution I have now is to divide SUM(q.quantity_value) by COUNT(n.title), it would work, but I think there is better way to do it :)

Thanks for your help!

Ed

Answer 1

Hum... Classic "I am sure I tried that before and it wasn't working"... I spent an entire day looking for the solution, and find it 5 minutes after posting here :)

$query = db_query("
  SELECT n.title, SUM(q.quantity_value)
  FROM {node} n
  JOIN {quantity} q
  ON q.entity_id = n.nid
  WHERE EXISTS (SELECT *
    FROM {tags} t
    WHERE t.entity_id = n.nid
    AND t.tid IN (12, 15))
    HAVING COUNT(DISTINCT t.tid) = count(12,15)
  GROUP BY n.title
");
$results = $query->fetchAll();

Subquery was the way to go (if anyone has something better to suggest, I would love to hear it).

Thanks,

Ed