Pomoč pri izdelavi MySQL poizvedbe...

... where (ime_atributa='velikost' and vrednost_atributa='L') or (ime_atributa='barva' and vrednost_atributa='modra')

select t1.id
from tabela_atributov t1
inner join tabela_atributov t2
 on t1.id=t2.id
where (t1.ime_atributa='barva' and t1.vrednost_atributa='modra')
 and (t2.ime_atributa='velikost' and t2.vrednost_atributa='L')

6bt9hmDwY je 7. jan 2022 ob 18:56 izjavil:

union sicer nastopa kot or - disjunkcija, ker vrača unijo zapisov velikost=L in barva=modra, ne pa preseka, kar op verjetno želi.

kuall je 7. jan 2022 ob 19:18 izjavil:

nesmiseln query.
ne more imet atribut z istim idjem različne vrednosti.

kuall je 7. jan 2022 ob 19:31 izjavil:

v queryju imaš samo 2 tabeli, ki sta enaki in ju joinaš po idju. ok pol si se zatipkal in si mislil, da je ena tabela od produktov? ampak nisi se mogel zatipkat, ker ju v where spet obravnavaš kot tabeli atributov.
priznaj, da si skracal nesmisel in nehaj bluzit no. :)

kuall je 7. jan 2022 ob 19:57 izjavil:

ti poskusi, napiše cel query pa ga bomo pognali. kaj bom jaz poskušal tvoje nesmisle in zgubljal čas heh.

| id | ime       |
| 1  | Kuall     |
| 2  | Junior    |
| 3  | Ne razume |

| id | ime      | vrednost |
| 1  | barva    | modra    |
| 2  | barva    | modra    |
| 1  | velikost | L        |
| 2  | velikost | S        |
| 3  | velikost | L        |

SELECT p.id, p.name
FROM products AS p 
WHERE
EXISTS
(select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
where a.slug='velikost' and av.slug = 'xs' and p.id = pa.product_id) 
and
EXISTS
(select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
where a.slug='barva' and av.slug = 'crna' and p.id = pa.product_id) 

urli je 8. jan 2022 ob 13:18 izjavil:

Hvala obema! Takole na hitro sem preveril spodnjo poizvedbo in mislim, da je to to! Ko utegnem, preverim še kakšno rešitev, da vidim, kako je s performance. Gnezdenje poizvedb mi v osnovi ne diši najbolje (performance?) ampak verjetno drugače tako ne bo šlo?

SELECT p.id, p.name
 FROM products AS p 
 WHERE
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='velikost' and av.slug = 'xs' and p.id = pa.product_id) 
 and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='barva' and av.slug = 'crna' and p.id = pa.product_id) 

SELECT p.id, p.name
 FROM products AS p 
 WHERE
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='velikost' and ( av.slug = 'xs' or av.slug = 'l' or av.slug = 'xs') and p.id = pa.product_id) 
 and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='barva' and av.slug = 'crna' and p.id = pa.product_id) 
  and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='ram' and av.slug = '1' and p.id = pa.product_id) 
   and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='tv' and av.slug = 'test' and p.id = pa.product_id) 
    and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='zaslon' and av.slug = '234' and p.id = pa.product_id) 
     and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='resolucija' and av.slug = '123' and p.id = pa.product_id)
      and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='resolucija' and av.slug = '123' and p.id = pa.product_id) 
       and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='resolucija' and av.slug = '123' and p.id = pa.product_id) 
        and
 EXISTS
 (select 1 from product_attributes AS pa LEFT JOIN attribute_values AS av ON (pa.attribute_value_id = av.id) LEFT JOIN attributes AS a ON (av.attribute_id = a.id) 
 where a.slug='resolucija' and av.slug = '123' and p.id = pa.product_id) 

https://i.postimg.cc/hcnhsPzb/explain.png

https://i.postimg.cc/5t8dvW85/pocasi.png

select Ime_izdelka
from ime_tabele
group by Ime_izdelka
having sum(case when Ime_atributa = 'velikost' and Vrednost_atributa = 'L' then 1
        when Ime_atributa = 'barva' and Vrednost_atributa = 'modra' then 1
        else 0 end) = 2

DamijanD je 9. jan 2022 ob 07:35 izjavil:

A se ne bi dalo na tak način (pač za vsak pogoj dodaš en inner join (italics) blok) in dobiš IDje produktov, ki jih pa potem uporabiš v glavnem queriju...

 select distinct p.id 
 from products p
 
 inner join (select pa.product_id 
 from product_attributes pa
 inner join attribute_values av ON pa.attribute_value_id = av.id
 inner JOIN attributes a ON av.attribute_id = a.id
 where a.slug='velikost' and av.slug = 'xs') f1 on f1.product_id = p.id
 
 inner join (select pa.product_id 
 from product_attributes pa
 inner join attribute_values av ON pa.attribute_value_id = av.id
 inner JOIN attributes a ON av.attribute_id = a.id
 where a.slug='barva' and av.slug = 'crna') f2 on f2.product_id = p.id
 

no comment je 9. jan 2022 ob 10:54 izjavil:

Dummy vzame 1s, ms pivot 45, tvoja 2min.

Tvoja in ms pivot rešitev bo na relativno kratki tabeli delala dovolj hitro za rezultate v "realnem" času. Ko je amatersku ure konec, je pa treba vsaj vedeti kaj bo naredilo index seek, kaj index scan...