How to query with filter classifications Looking for a way to filter clothes based on their labels, the labels need to be considered for their classifications, such that different classification all needs to be satisfied (AND), same classification is joined (OR).
E.g. The following expectation can be satisfied:
Ask: (Blue(classification: Color) or Green(classification: Color)) and On Sale(classification: Discount)
or
Ask: Blue(classification: Color) and (Aloha(classification: Style) or Knit(classification: Style))
Need help understand the best way to achieve this.
SELECT
    &quot;clothes&quot;.*
FROM
    &quot;clothes&quot;
INNER JOIN &quot;clothes_labels&quot; ON
    &quot;clothes_labels&quot;.&quot;clothes_id&quot; = &quot;clothes&quot;.&quot;id&quot;
INNER JOIN &quot;labels&quot; ON
    &quot;labels&quot;.&quot;id&quot; = &quot;clothes_labels&quot;.&quot;tag_id&quot;
WHERE
    ...


Sample query fiddle (thank you):
https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=fd625a0f5b97a6e6b3067758aadcd434
In Postgres, I would aggregate the labels for each clothe into a JSONB array which can then be queried for &quot;contains all of this&quot; quite easily.
Something along the lines:
select *
from clothes c
  join (
     select cl.clothes_id,
            jsonb_agg(to_jsonb(l) - 'id') as class
     from clothes_labels cl
       join labels l on cl.label_id = l.id
     group by cl.clothes_id
  ) cl on cl.clothes_id = c.id
where class @&gt; '[{&quot;name&quot;: &quot;Blue&quot;, &quot;classification&quot;: &quot;color&quot;},{&quot;name&quot;: &quot;Aloha&quot;, &quot;classification&quot;: &quot;style&quot;}]'
   or class @&gt; '[{&quot;name&quot;: &quot;Knit&quot;, &quot;classification&quot;: &quot;style&quot;}]'
;

Online example

In general I would replace EAV models with a JSON approach these days as they tend to be easier to query and work with (again: assuming Postgres, in MySQL this can't be indexed efficiently)