Autor Zpráva
Antonín
Profil *
#1 · Zasláno: 25. 1. 2023, 13:27:04
Odpovědět Citovat
Zdravím,

jelikož zase upravuji databázi a přidělávám si filtry, potřeboval bych poradit. Dodělal jsem si u produktů filtry ( velikost - S,M,L,XL,XS.. barva - modrá, zelená, červená aj. ).
Vše funguje, až na to, že mi to prochází pouze produkty - hlavní karty a jejich filtry.

Jednoduché řešení by bylo, kdyby všechny filtry které mají varianty vložil i k hlavní kartě....ale už teď má tabulka s filtry cca 300 tis řádků a druhá věc, pokud není skladem ta varianta s daným parametrem, tak by mi to neodfiltrovalo.

Když přidám OR, tak to bude pomalé na indexech.

Jde mi u tu část 
AND pr.id IN ( SELECT product_id FROM shop_products_filter  WHERE filter_id IN ( 29,36 ) )

SQL: 
SELECT pr.*, SUM(rt.rate) AS product_total_rate, prl.name,
COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock
WHERE prstock.product_id=pr.id
 ),0) as total_stock_pr, COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock 
 JOIN shop_products npr ON prstock.product_id=npr.id
WHERE npr.deleted=0 AND npr.archive=0 AND pr.id=npr.parent_id 
 ),0) as total_stock_var 
FROM shop_products pr
JOIN shop_products_category cat ON pr.id = cat.product_id
JOIN shop_products_lang prl ON pr.id = prl.product_id AND lang_id=1
LEFT JOIN shop_products_rate rt ON pr.id=rt.product_id
LEFT JOIN shop_products_tags tag ON pr.id = tag.product_id
WHERE pr.active=1 AND pr.archive=0 AND pr.parent_id=0 AND pr.deleted=0 AND cat.category_id=30 AND
pr.price>=20 AND pr.price<=20000 AND pr.id IN ( SELECT product_id FROM shop_products_filter  WHERE filter_id IN ( 29,36 ) )
GROUP BY pr.id
HAVING SUM(total_stock_pr+total_stock_var) > 0
ORDER BY pr.priority_show DESC, pr.id 
LIMIT 0, 50;


tabulka shop_products 

id INT(AI, PRIMARY),
parent_id INT (key)

..

tabulka shop_products_filter = 
id INT( AI, PRIMARY KEY)
product_id INT (key)
filter_id INT (key)

tabulka shop_products_category = 
id INT( AI, PRIMARY KEY)
product_id INT (key)
category_id INT (key)

Dále se chci zeptat, jak postupovat, když chci aby se vše filtrovalo i s variantami (např. má varianta jinou cenu než hlavní karta - ta má nastaveno 599 ale je tam tričko i za 400 i za 800)

Díky za pomoc
Kajman
Profil
#2 · Zasláno: 26. 1. 2023, 07:36:53
Odpovědět Citovat
Moc ty filtry nechápu. Co znamená to 29, 36?

Když v eshopu hledám velikost L nebo XL v zelené barvě, tak jak vypadá sestavená podmíka na filtry?
Antonín
Profil *
#3 · Zasláno: 26. 1. 2023, 09:09:03 · Upravil/a: Antonín o 4 minuty později
Odpovědět Citovat
Pardon, zapoměl jsem napsat, že mám tabulku:

shop_filters
id INT( AI, PRIMARY KEY)
parent_id INT
active TINYINT
name VARCHAR(100)
info TEXT


A v ní záznamy

id / parent_id / active / name
1 / 0 / 1 / Velikost
2 / 1 / 1 / XL
3 / 1 / 1 / XXL

..to 29, 30 je vybrané ID filtru, které je u produktů přiřazeno


Tedy, 29 = S, 30 = Hnědá (např.)
Varianty produktů (pr.parent_id) mají taky přiřazeno ty filtry v tabulce shop_products_filter...
jenže já to hledám pouze u rodičů / hlavních produktů (a ty neznají filtry variant )
Kajman
Profil
#4 · Zasláno: 26. 1. 2023, 15:00:15 · Upravil/a: Kajman o 6 minut později
Odpovědět Citovat
Když zadám, že chci hnědé triko velikosti S, tak bych měl vidět právě ty hnědé varianty a nechci přece nadřazený produkt, co tomu neodpovídá, takže bych neomezoval hledání na pr.parent_id=0. Také chci, aby to našlo jen hnědá trika A velikosti S. Ne produkty, které jsou hnědé nebo velikosti S. A když si zatrhnu, zelenou, hnědou a velikost S, tak chci hledat (zelenou nebo hnědou) a velikost S.

Taky netuším, proč název parametru a jeho hodnotu cpete do jedné tabulky, ale ty "a" a "nebo" podmínky na vyhovující produkty půjdou asi psát jako 
SELECT pf.product_id
FROM   shop_products_filter pf
       JOIN shop_filters f
         ON pf.id = f.filter_id
WHERE  pf.filter_id IN ( 29, 35, 36 ) -- S, zelená, hnědá
GROUP BY pf.product_id -- doplneno pri editaci
HAVING Count(DISTINCT f.parent_id) = 2 -- musí být nalezena velikost i barva

Pokud je ten dotaz v in pomalý, můžete ten výsledek zkusit použít v joinu. U starých verzích mysql to občas pomohlo.
Antonín
Profil *
#5 · Zasláno: 27. 1. 2023, 09:28:15
Odpovědět Citovat
Zapomněl jsem zmínit - jde o filtr v kategorii obchodu, kde se nevypisují varianty zvlášť.

V kategorii je hlavní produkt např. Tričko, a po najetí na něj se mi zobrazí dostupné varianty (barevné kolečka) na fotce, viz např. aboutyou...když si odfiltruji jen velikost XS, S...taky mi to pořád ukazuje jen hlavní produkt => který má skladem tyto varianty, neukáže mi to vedle sebe dva stejná trička ve 2 velikostech.

A pokud podle vybraných filtrů sám neodpovídá (protože to nemusí být jen tričko ale i produkt bez variant), nebo není nalezena žádná varianta s těmito filtry, tak by se neměl zobrazovat.

Nejedná se tedy jen o trička, ale třeba příklad "vodítko pro psy" - varianty 1m, 2m, 3m, 5m, 10m, 15m. Ale produkt stále 1, jen s 5 variantama.

Filtry mám v 1 tabulce (shop_filter), protože s touto tabulkou nijak dál nepracuji, pouze mi vypíše ve filtrech Název - hodnoty a checkbox (filtr_id hodnoty), který se pak posílá do SQL
Kajman
Profil
#6 · Zasláno: 27. 1. 2023, 09:49:12 · Upravil/a: Kajman 27. 1. 2023, 14:08:50
Odpovědět Citovat
Takže si najdu vodítko, co má 5 metrů, ale když kliknu na eshopu, tak se nezapamatuje, že jsem chtěl 5 metrů a musím to znovu vybírat. V takovém eshopu bych nekupoval.

Ale na hlavní produkty si stačí k výsledku filtru přilinkovat tabulku produktů pro převod z podproduktu na hlavní produkt. 
SELECT DISTINCT Coalesce(Nullif(p.parent_id, 0), p.id) id
FROM   shop_products p
       JOIN (SELECT pf.product_id
             FROM   shop_products_filter pf
                    JOIN shop_filters f
                      ON pf.id = f.filter_id
             WHERE  pf.filter_id IN ( 29, 35, 36 ) -- S, zelená, hnědá
             GROUP  BY pf.product_id
             HAVING Count(DISTINCT f.parent_id) = 2 -- musí být nalezena velikost i barva
            ) nf
         ON p.id = nf.product_id

To číslo 2 bude asi nejjednodušší vypočítat dotazem předtím. Počet jedinečných filtrových parentů pro všechny výběrové filtry. 
SELECT Count(DISTINCT f.parent_id) pocet
FROM   shop_filters f
WHERE  f.filter_id IN ( 29, 35, 36 )
Podmínka s HAVING bude potřeba jen, pokud ten pocet vyjde více než 1.
Antonín
Profil *
#7 · Zasláno: 27. 1. 2023, 11:01:05
Odpovědět Citovat
Kajmane to tak ale funguje, když zatrhnu 5m a 10m (oba filtry) tak přeci mu neukážu vedle sebe 2 stejné vodítka, které mají stejný odkaz do 1 hlavního produktu, kde je vybraná varianta :)

Mě v těch filtrech ale parent na filter (barva, velikost) vůbec nezajímá, vůbec ho k ničemu nepotřebuji, ani nepotřebuji připojit tabulku shop_filters .... XL, S, 5M, 10M je jedinečné ID, ať už je to např.
Tričko => XL (ID 10)
Kalhoty => XL (ID 223)
Mikina => XL (ID 331)

to parent_id filtru je vlastně jen jakási kategorie hodnoty, stejně jako když připojuji tabulku shop_products_category => taky nepřipojuji shop_category kde mám vypsané kategorie.
Kajman
Profil
#8 · Zasláno: 27. 1. 2023, 14:08:13 · Upravil/a: Kajman o minutu později
Odpovědět Citovat
Antonín:
„Mě v těch filtrech ale parent na filter (barva, velikost) vůbec nezajímá, vůbec ho k ničemu nepotřebuji, ani nepotřebuji připojit tabulku shop_filters“

Je to potřeba na tu podmínku v having. Jinak stačí aby vyhledávání vyhověla jedna jediná podmínka i když si uživatel naklikal kombinaci parametrů. Když zadá, že chce zelené triko velikosti XL, tak mu ukážeš i trička, co jsou jen zelená, ale nejsou velikosti XL. Tím, že se tam připojí informace, zda to je barva nebo velikost, tak se může vyfiltrovat v having počtem různých parentů, že se našla i barva i velikost.
Antonín
Profil *
#9 · Zasláno: 29. 1. 2023, 16:23:59
Odpovědět Citovat
Super díky a máš pravdu, už jsem to vyzkoušel, zapomněl jsem na to, cos říkal, že mi to vlastně bude vyhledávat i trička S i barvy, i když nemá obojí.
Mám to snad hotové, celkově ty záznamy (15 tis produktů -- 57 tis řádků s filtry -- 34 tis kategorie -- 30 tis jazyky ...)
A v kategorii 8 tis produktů, celkem všechny 4 sql cca 500ms

Mám 4x SQL

1/ zjistí počet záznamů dle filtrů, min a max. cenu (pro cenový filtr)
2/ vypisuje data produktů
3/ mi vyhledá aktivní možné výrobce (dle daných filtrů)
4/ mi vyhledává "povolené" filtry a počet. - tady ale narážím na problém s tímto SQL. Problém je v tom, že mám Kategorie + checkboxy, př.

Trička

S....(10),
L.....(11),
M......(3),
XL ......(52)

Barva

Modrá......(33)
Hnědá .......(26)
Bíla.......(11)
Zelená.....(49)

A teď, nastává mi problém v tom, že se mi dynamicky (pomocí sql 4) mění checkbox na disabled a počet. Disabled je v pohodě, ale počet ne.
Jakmile zathnu pouze "S" přepočítá mi to i filtry v této kategorii (L ukáže např. 0 atd.), ale když na něj kliknu na L, tak mi to zase ukáže zpátky číslo které tam bylo.

Posílám i SQL 2, jestli jsem to postavil dle tvé rady správně nevím, zrovna nemám zapojené filtry u variant produktů, na hlavní produkty to snad funguje správně, jen mi nesedí ten začátek s distinct...jestli jsem to dobře použil

SQL 2/ - 
SELECT DISTINCT Coalesce(Nullif(pr.parent_id, 0), pr.id) id, pr.*, SUM(rt.rate) AS
product_total_rate, prl.name, prl.urlname, prl.short_description, COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock
WHERE prstock.product_id=pr.id
 ),0) as total_stock_pr, COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock 
 JOIN shop_products npr ON prstock.product_id=npr.id
WHERE npr.deleted=0 AND npr.archive=0 AND pr.id=npr.parent_id 
 ),0) as total_stock_var 
FROM shop_products pr
JOIN (
SELECT pfilter.product_id 
FROM shop_products_filter pfilter 
 JOIN shop_filter flt ON pfilter.filter_id = flt.id
WHERE pfilter.filter_id IN ( 29 )
GROUP BY pfilter.product_id
HAVING Count(DISTINCT flt.parent_id) = 1
 ) nf ON pr.id = nf.product_id
JOIN shop_products_lang prl ON pr.id = prl.product_id AND prl.lang_id=1
LEFT JOIN shop_products_rate rt ON pr.id=rt.product_id
WHERE pr.active=1 AND pr.archive=0 AND pr.parent_id=0 AND pr.deleted=0 AND pr.id IN ( 
SELECT product_id FROM shop_products_category WHERE category_id = 30 )
GROUP BY pr.id
HAVING SUM( total_stock_pr+total_stock_var ) > 0
ORDER BY pr.id
LIMIT 0, 20

SQL 4/
(vrací jen záznamy ID FILTRU - POČET PRODUKTŮ ) 

SELECT d1.total, d1.filter_id AS filter_id 
FROM (
SELECT COUNT(pr.id) AS total, prfilter.filter_id, COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock
WHERE prstock.product_id=pr.id
    ),0) as total_stock_pr, COALESCE((
SELECT SUM(prstock.amount) 
FROM shop_products_stock prstock 
 JOIN shop_products npr ON pr.id=npr.parent_id
WHERE npr.deleted=0 AND npr.archive=0 AND prstock.product_id=pr.parent_id
    ),0) as total_stock_var 
FROM shop_products pr
JOIN (
 SELECT pfilter.product_id 
 FROM shop_products_filter pfilter 
   JOIN shop_filter flt ON pfilter.filter_id = flt.id
   WHERE pfilter.filter_id IN ( 29 )
   GROUP BY pfilter.product_id
   HAVING Count(DISTINCT flt.parent_id) = 1
) nf ON pr.id = nf.product_id
JOIN shop_products_lang prl ON pr.id = prl.product_id AND prl.lang_id=1
JOIN  shop_products_filter prfilter ON pr.id = prfilter.product_id
WHERE pr.active=1 AND pr.archive=0 AND pr.parent_id=0 AND pr.deleted=0 AND pr.id IN ( 
SELECT product_id FROM shop_products_category WHERE category_id = 30 )
GROUP BY prfilter.filter_id
) d1
GROUP BY d1.filter_id
Kajman
Profil
#10 · Zasláno: 29. 1. 2023, 20:41:04
Odpovědět Citovat
Antonín:
„Jakmile zathnu pouze "S" přepočítá mi to i filtry v této kategorii (L ukáže např. 0 atd.), ale když na něj kliknu na L, tak mi to zase ukáže zpátky číslo které tam bylo.“

Protože filtry se stejným rodičem (např. velikost trička) se navzájem nevylučují. Tam platí to OR - takže když mám S, tak u L by mělo být (+11). Nejsem si jistý jak to udělat v jednom dotaze. Ale určitě by to šlo tak, že se pro každou skupinu filtrů, kde je alespoň jeden zatrhnutý, udělá zvlášt výpočet bez omezení zatrhnutí v této skupině.

A v dotaze 2 to je asi špatně použité. Ten dotaz se musí uzávorkovat a použít v in nebo v join na produkty, aby se braly ty hlavní. V aliasu pr mohou být přece podprodukty.
Časová prodleva: 2 roky

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0