[MySQL] Produkty z kategorii i subkategorii

[MaxGaming]

Witam wszystkich.

Mam tabelę z produktami która wygląda mniej więcej tak(w dużym uproszczeniu): id | name | category | date.

Kategorie są dwupoziomowe to znaczy np.

Cytuj

 

Kategoria: Komputery

Podkategorie: Laptopy, PC.

Kategoria: Telefony.

Podkategorie: Android, Windows Phone, iOS.

 

A tabela z kategoriami wygląda tak:

Cytuj

 

id | name | parent

1 Komputery 0

2 Laptopy 1

3 PC 1

4 Telefony 0

5 Android 4

6 Windows Phone 4

7 iOS 4

 

Czyli jeżeli parent = 0 to jest to kategoria a jeżeli np 4 to oznacza że jest to podkategoria kategorii o id 4(czyli w wypadku tego przykładu telefonów).

 

Teraz tak, w każdej kategorii są produkty, ale produkty mają jak widać wyżej tylko id dokładnie subkategorii.

W wypadku wejścia w subkategorię wygląda zapytanie tak:

SELECT * FROM product WHERE category = ".$subcategory_id." ORDER BY date;

I np w wypadku Telefony>Android:

SELECT * FROM product WHERE category = 4 ORDER BY date;

A jak wyciągnąć wszystkie produkty z kategorii Telefony?

Najpierw rozbiłem to na kilka zapytań. Sprawdzałem jakie subkategorie mają jako ojca daną kategorię i w php pętlą for z każdej wyciągałem produkty, ale raz że to raczej mało optymalne dwa że potem osobno trzeba sortować(sortuje wtedy każde zapytanie osobno po dacie). Potrzebuje czegoś w stylu(pseudokod):

SELECT * FROM product WHERE (category WHERE parent = 4) ORDER BY date;

Czyli w wypadku gdy użytkownik chce zobaczyć wszystkie komputery niezależnie od podkategorii muszę wyciągnąć produkty z kategorii których rodzicem jest podane id(w tym przykładzie 4). Jeżeli nie zbyt jasno wyjaśniłem pytajcie ale mam nadzieję że rozumiecie o co mi chodzi :/

[SLy]

Jeżeli kategorie są tylko dwupoziomowe to można na przykład tak:

select * from product where category = 4 or category in (select id from category where parent = 4) order by date

 

[gnysek]

Zapisz sobie w czwartej kolumnie "main category" Będzie szybciej niż subselecty w zapytaniu.

 

Kategorie to nie są jakieś dane, których masz milion i zajmują 100MB w bazie, więc dorobienie jednej kolumny w niczym nie przeszkadza, a ogólnie jak tworzysz strukturę tabeli to powinna być taka, żeby prędkość pobierania zawsze była priorytetem, a jeśli tabela przewiduje 100-200 wpisów, to i 300 kolumn w niczym nie przeszkadza

[MaxGaming]

@gnysek jak dobrze CIę rozumiem to za to odpowiada kolumna parent. Ona wskazuje nadrzędną kategorie.

@SLy tak, są tylko dwa poziomy. Działa Twój sposób.

Dzięki wielkie obu panom

[gnysek]

@MaxGaming ale rozumiem, że moze być taka struktura

 

Kategoria główna

-Kategoria podrzędna

--Kategoria jeszcze bardziej podrzędna

 

wtedy by trzeba kolumny "root", żeby zawsze tą "główną" wskazywać, to będzie szybciej (ogólnie mysql średnio się nadaje do drzewek). Jak masz tylko jednen poziom podkategorii to wystarczy:

 

select * from product where category = 4 or category = parent

Jak masz klika poziomów kategorii i chcesz wszystkie zawrzeć, to niestety jest rzeźbienia trochę i wydajność takiego zapytania może szybko spaść.

[Threef]

Jeżeli kategorii nie ma zbytu dużo to możesz je zapisywać w formie potęgi 2 a kategorię z podkategoriami trzymać jako jeden int. Potem wykonywać operacje binarne. 

 

Czyli kategorie wyglądałby tak:

id | name

1 Komputery

2 Laptopy

4 PC

8 Telefony

16 Android

32 Windows Phone

64 iOS

 

Wtedy telefon z androidem miałby kategorię 24. A komputer PC 5

 

Ale pierw poczytaj o postaci normalnej w bazach danych

 

[Ranmus]

@MaxGaming w porządnych bazach danych SQL używa się do tego dyrektywy WITH RECURSIVE. Sprawdziłem przed chwilą i niby w MySQL 8.0 już to jest i z pamięci MariaDB 10.0.2.x: https://dev.mysql.com/doc/refman/8.0/en/with.html

 

Tutaj masz przykład PostgreSQL. Sądzę, że powininien zadziałać bez poprawek z najnowszym MySQLem, bo w Oracle jest ta sama składnia tego, a więc pewnie nie zrobili żadnego wyłamu (ten sam producent):

SELECT * FROM products p WHERE p.category IN
(
  WITH RECURSIVE subcategories AS
  (
    SELECT c.id from categories c WHERE id = 4
    UNION ALL
    SELECT c.id FROM categories c, subcategories s WHERE s.id = c.parent_id
  )
  SELECT s.id FROM subcategories s
)

Jest to proste zapytanie, którym wyciągasz wszystkie produkty z kategorii o id 4 i wszystkich podkategorii. Aby ustalić podkategorie, za pomocą WITH RECURSIVE tworzysz rekursję, która składa się z unii dwóch zapytań. Pierwsze zapytanie wyciąga element pierwszy szukany (nadrzędny), a drugie zapytanie szuka kolejne pasujące elementy, czyli w naszym przypadku podkategorie. Do tego za wewnętrznym nawiasem jest zapytanie ustalające co chcemy wybrać z tych wyszukanych rekursywnie elementów. Oczywiście chcemy tylko id, żeby od razu przekazać je jako argument IN zapytania głównego. Zwracam uwagę, że nazwa "subcategories" to nazwa rekursji, którą Ty ustalasz.

 

Złożoność obliczeniowa logarytmiczna (czyli jest szybko), bez zbędnych kolumn, operacji bitowych i innych takich.

 

 

 

[gnysek]

Dnia 16.09.2017 o 00:53, Ranmus napisał:

@MaxGaming w porządnych bazach danych SQL używa się do tego dyrektywy WITH RECURSIVE. Sprawdziłem przed chwilą i niby w MySQL 8.0 już to jest i z pamięci MariaDB 10.0.2.x: https://dev.mysql.com/doc/refman/8.0/en/with.htm

 

Obawiam się, że bez VPSa to masz wszędzie MySQL 5.5 - 5.x - ale dobrze wiedzieć, że jest już taka opcja. Niestety sporo ficzerów baz danych jest zazwyczaj niewykorzystywana (brak wiedzy) lub bywa zapomniana, że warto z niej korzystać (np. triggery, widoki, czasem nawet powiązania tabel itp.).