[MySQL] zapytanie zwracające produkty należące do kategorii

Mam bazę danych jak poniżej:
http://sqlfiddle.com/#!9/db3f5f/3

Zastosowana jest tutaj relacja jeden do wielu (jeden produkt może należeć do kilku kategorii).
Przykładowe zapytanie z dema zwraca listę kategorii (ich id) do których należy dany produkt ( w tym przypadku produkt o id = 3).

Moje pytanie brzmi.
Jak stworzyć zapytanie, które zwróci mi wszystkie produkty należące do podanej/podanych kategorii.

Przykładowo. Chcę wyświetlić produkty (ich nazwy oraz id), które należą zarówno do kategorii Elektronika oraz Telefon (czyli id_cat = 2 oraz 3).
Oczywiście zapytanie musi radzić sobie ze zmienną kolejnością (id_cat = 3 oraz 2).

Dodam, że w przypadku mojej oryginalnej bazy lista kategorii czasem może mieć nawet po 20 elementów (lub więcej). Fajnie jakby dało się to zrobić w miarę optymalnie i prosto.

Pewnie da się łatwiej ale działa:
http://sqlfiddle.com/#!9/ab3d38/8

Bardzo podobnie do @hippek

http://sqlfiddle.com/#!9/d4ce3b/7

select 
    id_prod
    ,name
from
    prod
where 
	id_prod in (SELECT 
					id_prod 
				from 
					cat_prod
				WHERE 
					id_cat in  (3,2)
				group by 
					id_prod
				having count(distinct id_cat) = (select count(*) from cat where id_cat in  (3,2))
				)

Różnice do poprzednika:

1. Join w podzapytaniu nie jest potrzebny wystarczy sama tabela cat_prod
2. w warunku having używam podzapytania do obliczenia ile tych kategori jest
3. zliczam tylko pojedyncze wystapienia id_cat, aby uniknąć sytuacji, że produkt został dodany 2 razy do tej samej kategori

p.s. zmieniłem dane w fiddle, bo nie było produktu który by byl tylko z 3 lub 2 kategarią i inną

Możesz jeszcze tak łopatologicznie wytłumaczyć po co ten having? W sensie po co mam mu podać tą ilość kategorii, w których szukam?

Dla danych które zmieniłem w fiddle, zapytanie:

SELECT * from cat_prod
WHERE id_cat in  (3,2)

Zwróci

Jak widać produkty 4,3 nalezą do obu szukanych kategorii:2,3, a produkt 2 tylko do kategori 3

więc zapytanie

SELECT id_prod,count(distinct id_cat) from cat_prod
WHERE id_cat in  (3,2)
group by 
id_prod

Zwróci:

Dlatego potrzebujesz sprawdzić ile tych categori ma produkt, aby wiedzieć, że należą do wszystkich szukanych.
Puść moje zapytanie bez warunku having i zobacz różnicę w wynikach.

Zapytanie działa, ale niestety nie dla mojej bazy :)
Zapomniałem, że mam strukturę hierarchiczną kategorii (id_parent)

1. Nowe demo z nową bazą
   http://sqlfiddle.com/#!9/b20451

2. Rozrysowane drzewo kategorii
   http://vpx.pl/i/2018/03/10/1.png

Problemem jest filtrowanie tych produktów, które chcę załatwić zapytaniem.
Daję użytkownikowi możliwość filtrowania po każdej z kategorii na zasadzie 3 inputów typu select (każdy input select rozwija drzewo kategorii począwszy od głównej kategorii, dla której id_parent = 0). Niewybranie którejś z opcji oznacza szukanie w całej kategorii głównej.

Przykładowo ustawiając inputy typu select na
a) - (nie wybrany czyli szukamy każdego TYPU nadwozia)
b) Audi
c) Perłowy

Powinny wyświetlić się produkty:
(2, 'Audi A6 C6 Perłowy Niebieski i Biały Kombi', 87000),
(6, 'Audi A3 Perłowy Biały Hatchback', 20150),

Mam nadzieję, że dość jasno to wszystko napisałem. Gdyby coś trzeba było sprostować to proszę o info co :)

Tu jest problem, ze tak naprawdę tabela kategorii zawiera 3 struktury: typ, producent i kolor, w przykładzie o którym piszesz masz pytanie o producenta i kolor.
dodałem widok cat_tree, który zwraca strukturę kategorii, zakładam, że tabela cat_prod, zawiera informacje o najgłębszym zagnieżdżeniu, tym sposobem można te dane pobrać tak:

select
    *
from 
   prod
where
    id_prod in (
  select distinct 
      kolor.id_prod
  from (select 
          id_prod
      from 
          cat_prod
      where 
          id_cat in (select 
                          COALESCE(lv4i, lv3i, lv2i, lv1i)
                      from 
                          cat_tree
                      where 
                          lv2 = 'perłowy')
      ) kolor
      inner join (select 
                      id_prod 
                  from 
                      cat_prod
                  where 
                      id_cat in (select 
                                      COALESCE(lv4i, lv3i, lv2i, lv1i)
                                  from 
                                      cat_tree
                                  where lv2 ='audi')
                  ) producent on producent.id_prod = kolor.id_prod
)

http://sqlfiddle.com/#!9/53ccc6/6

Niestety nie ma informacji o poziomie zagłębienia. Mogę ją jednak wyłuskać w PHP przeglądając drzewo kategorii do najniższego poziomu.

Nie rozumiem, jak chcesz szukać nie wiedząc gdzie?
Zakładam, że w jakiś sposób generujesz użytkownikowi dane do zapytania, czy to jest na podstawie jednego input-a w który wpisujesz szukaną informację?

Są trzy inputy typu select
1). Pierwszy rozwija całe drzewo kategorii TYP
2). Drugi rozwija całe drzewo kategorii PRODUCENT
3) Trzeci rozwija całe drzewo kategorii KOLOR

Czyli jeśli w polu 1). wybierze "Sedan", to mam id kategorii = 2 - jako, że jest to najniższy poziom w tym drzewie kategorii, to szukany produkt musi być przypisany do tej kategorii ( o id 2) - inaczej nie spełniał by warunku stawianego przez użytkownika.
Jeśli jako producenta 2). wybierze "Seria X", to wiem, że id tej kategorii = 20 i szukany produkt (oprócz powyższego warunku) musi również należeć do kategorii 21, 22 lub 23
Pozostaje jeszcze 3). czyli kolor. Jeśli tutaj dla przykładu nic nie wybierze (bo kolor go nie interesuje), to dodatkowym warunkiem zapytania jest, że produkt musi oprócz powyższych należeć do kategorii 34, 35, 36, 37, 38, 39, 40, 41,42 lub 43

Ważne jest tutaj słowo lub dla każdego warunku z osobna. Łącząc jednak wszystkie trzy warunki ze sobą musi to być AND
czyli dla w/w przykładu
id_cat = 2 AND id_cat IN(21,22,23) AND id_cat IN (34, 35, 36, 37, 38, 39, 40, 41,42, 43)

Może mój tok rozumowania jest błędny? Jeśli tak to może jakaś sugestia?
Jedno jest pewne - nie mogę zmienić struktury bazy danych i muszę mieć możliwość filtrowania wyników podobnej do w/w rozpiski (po typie, kolorze oraz producencie).

Jak już wspomniałem poziomy i inne tego typu rzeczy mogę obliczyć z poziomu PHP przeglądając drzewo kategorii.

Pokaż co masz za wartości w tym selecta dla producenta, bo chce mieć pewność że dobrze zrozumiałem, ogranicz do audi.

Widoki możesz dodawać, czy w ogóle nie możesz ingerować w bazę?

http://vpx.pl/i/2018/03/12/187ee03ac369067c1.jpg

Mogę wykonać dowolne zapytanie do bazy. Nie mogę tabel zmieniać.

"value" to id danej kategorii/podkategorii.

PS
Nie dostałem powiadomienia dlatego tak długo nie pisałem ;P

Tabela nie możesz zmieniać, ale widoki dodać możesz? Nie jest to konieczne, ale wpłynie na czytelność zapytania unikniesz podzapytań...

No tak :D
Praktycznie każde zapytanie mogę wykonać o ile nie ingeruje w strukturę tabel (nie dodaje nic, nie zmienia typów itp).
Widok jest OK.
Ale on chyba będzie musiał być tworzony za każdym razem? Powiedzmy że w tym samym czasie jest 10 userów i każdy wybiera inaczej filtry...

Ja bym zamiast trawersować, przeszedł na metodę ścieżek wtedy latwiej odpytać o konkretne kategorie, czyli po kolei:

Stworzyłem widok cat_path:

create view cat_path
as
select 0 lvl, t0.id_cat,concat_ws('/',t0.id_cat) node_path,t0.name from
    cat t0
where
t0.id_parent=0
union all
select 1 lvl, t2.id_cat,concat_ws('/',t1.id_cat,t2.id_cat),t2.name from
    cat t1
    inner join cat t2 on t2.id_parent = t1.id_cat
where
t1.id_parent=0
union all
select 2 lvl,  t3.id_cat,concat_ws('/',t1.id_cat,t2.id_cat,t3.id_cat),t3.name from
    cat t1
    inner join cat t2 on t2.id_parent = t1.id_cat
    inner join cat t3 on t3.id_parent = t2.id_cat
where
t1.id_parent=0
union all
select 3 lvl, t4.id_cat,concat_ws('/',t1.id_cat,t2.id_cat,t3.id_cat,t4.id_cat),t4.name from
    cat t1
    inner join cat t2 on t2.id_parent = t1.id_cat
    inner join cat t3 on t3.id_parent = t2.id_cat
    inner join cat t4 on t4.id_parent = t3.id_cat
where
t1.id_parent=0

który zwraca takie dane

Ważna jest kolumna node_path, bo to ona powinna się znaleźć w value option, dla przykładu dane dla producenta:

select node_path value_option,concat(repeat('-',(lvl-1)*2),name) option_innerhtml from cat_path
where node_path like '6/%'
order by 1

Wynik

Czyli wracając do przykładu mam wybranego

1. Producenta: Audi: 6/7
2. Kolor Perłowy: 33/41

Dodałem widok cat_prod_path tylko po to by dodać kolumnę node path do tabeli cat_prod.

I teraz jedynie co potrzebujemy to wykorzystać zapytanie, już podane wcześniej, ale wykorzystując kolumnę node_path:

SELECT
    *
FROM 
   prod
WHERE
    id_prod IN (
  SELECT DISTINCT 
      kolor.id_prod
  FROM (SELECT 
          id_prod
      FROM 
          cat_prod
      WHERE 
          id_cat IN (SELECT 
                       id_cat
                     FROM 
                       cat_prod_path
                     WHERE node_path like'33/41%')
      ) kolor
      INNER JOIN (SELECT 
                      id_prod 
                  FROM 
                      cat_prod
                  WHERE 
                      id_cat IN (SELECT 
                                      id_cat
                                  FROM 
                                      cat_prod_path
                                  WHERE node_path like'6/7%')
                  ) producent ON producent.id_prod = kolor.id_prod
)

Przeanalizuj: http://sqlfiddle.com/#!9/13a594/3

By nie zaciemniac poprzedniego posta:

Ale on chyba będzie musiał być tworzony za każdym razem? Powiedzmy że w tym samym czasie jest 10 userów i każdy wybiera inaczej filtry...

Nie widok tworzysz raz, ma on ułatwiać pisanie zapytań i ilośc użytkowników nie ma tu nic do rzeczy.

Działa super.
Jeszcze nie wiem jak - muszę nad tym przysiąść i przekminić.

Wielkie dzięki za cierpliwość i pomoc. Jeśli coś będzie niejasne pozwolę sobie napisać!

Nie widok tworzysz raz, ma on ułatwiać pisanie zapytań i ilośc użytkowników nie ma tu nic do rzeczy.

Podejrzewam, że każdorazowo przy zmianie struktury? Czyli np jak dojdzie mi nowy producent (przykładowo FIAT).

Tak mnie tknęło, że zasugerowałem sie poprzednim zapytaniem, a to można dużo prościej, to jest fajny przyklad jak wykorzystać join-a do filtrowania rekordów, no i będziesz wiedział jak dodać filtrowanie po typie, po prostu dopiszesz join-a ;)

SELECT distinct
    prod.*
FROM 
   prod
   /*join do filtrowania po producencie, jak nie ma to nie umieszczamy w zapytaniu*/
   inner join cat_prod_path producent on producent.id_prod=prod.id_prod 
                                         and producent.node_path like'6/7%'
   /*join do filtrowania po kolorze, jak nie ma to nie umieszczamy w zapytaniu*/
   inner join cat_prod_path kolor on kolor.id_prod=prod.id_prod 
                                     and kolor.node_path like'33/41%'

Podejrzewam, że każdorazowo przy zmianie struktury? Czyli np jak dojdzie mi nowy producent (przykładowo FIAT).

Nie. Widok pobiera dane z tabel, obrazowo jak dodasz dane do tabeli to znajdą się w widoku który z tych tabel korzysta.

Nie. Widok pobiera dane z tabel, obrazowo jak dodasz dane do tabeli to znajdą się w widoku który z tych tabel korzysta.

Nawet jeśli pojawi się lvl 4?
Nie wiem czy ogarnąłem ale widok stworzony jest jedynie do lvl3 (jak niżej)

UNION ALL
SELECT 3 lvl, t4.id_cat,concat_ws('/',t1.id_cat,t2.id_cat,t3.id_cat,t4.id_cat),t4.name FROM
    cat t1
    INNER JOIN cat t2 ON t2.id_parent = t1.id_cat
    INNER JOIN cat t3 ON t3.id_parent = t2.id_cat
    INNER JOIN cat t4 ON t4.id_parent = t3.id_cat
WHERE
t1.id_parent=0

Dodanie poziomu będzie wymagało zmiany widoku. Dodanie wartości do nie.