Query polymorphic relation table

Question

I'm trying to query the following table:

+----+------------------+----------------+-------------+-------+
| id | describable_type | describable_id | property_id | value |
+----+------------------+----------------+-------------+-------+
|  1 | product          |              1 |           6 | Red   |
|  2 | product          |              2 |           6 | Blue  |
|  3 | product          |              1 |           4 | 150   |
|  4 | product          |              2 |           4 | 150   |
+----+------------------+----------------+-------------+-------+

I want all describable_id where the value of property_id 6 is Red and property_id 4 is 150.

I just cannot figure out how to query this.

Answer 1

SELECT describable_id
FROM table
WHERE (property_id, value) IN ((6, 'Red'), (4, '150'))
GROUP BY describable_id
HAVING COUNT(DISTINCT property_id, value) = 2

For strongly 2 pairs

SELECT DISTINCT t1.describable_id
FROM table t1
JOIN table t2 ON t1.describable_id = t2.describable_id
WHERE t1.property_id = 6
  AND t1.value = 'Red'
  AND t1.property_id = 4
  AND t1.value = '150'

If (property_id, value) is unique (by index!) you may remove DISTINCT from both queries.