Zapytanie wyszukujące na podstawie parametrów (tak jak na allegro) Opcje

[lorak110786]

Witam. Mam następujący problem:
Są 2 tabele:
produkty, parametry

w tabeli 'produkty' są pola:
id_produktu, nazwa

w tabeli 'parametry' są pola:
id_produktu, parametr, wartosc

Omawiając. Każdy produkt ma kilka parametrów. Każdy parametr ma jakąś wartość. Mam 3 produkty: pr1, pr2, pr3. Mam też ogólnie 3 parametry: wys, szer, waga. Łącznie w tabeli 'parametry' mam 9 rekordów, po 3 pary >parametr i wartość parametru< dla każdego z produktów.

Chciałbym napisać takie zapytanie które wybierze mi produkty spełniające WSZYSTKIE określone warunki w jednym zapytaniu czyli np: 'wys' BETWEEN 30 AND 100, oraz 'szer' BETWEEN 60 AND 100 oraz 'waga' BETWEEN 10 AND 100

Próbowałem już kombinować na różne sposoby, ale nie chce mi wyjść. Otrzymuję albo wyniki produktów spełniających dowolne kryterium, lub jeśli w HAVING użyję "AND" - brak wyników, co jest oczywiste gdyż nie ma dwóch rekordów spełniających warunki na AND.

[SQL] pobierz, plaintext 
SELECT * 
FROM products
LEFT JOIN products_values vals USING(id_product)
HAVING (vals.id_label=61 AND vals.value  BETWEEN 100 AND 500) OR (vals.id_label=63 AND vals.value  BETWEEN 15 AND 30) 
[SQL] pobierz, plaintext 

Ma ktoś na to jakiś mądry sposób? Z góry dziękuję za podpowiedzi.

Ten post edytował lorak110786 5.10.2010, 14:40:09

[TheTester]

Nie wiem jak to zadziała, bo pisane bez sprawdzania, ale ja bym to chyba tak zrobił:

[SQL] pobierz, plaintext 
SELECT * FROM products p, products_values v WHERE p.id=v.id_product AND ((v.id_label=61 AND v.value BETWEEN 100 AND 50) OR (v.id_label=63 AND v.value BETWEEN 15 AND 30))
[SQL] pobierz, plaintext 

[lorak110786]

No to jest pierwsza rzecz jaką próbowałem zrobić. Niestety wybiera źle. Wybiera rekordy które spełniają dowolne z warunków (gdyż użyte jest OR).

[TheTester]

Hmm...Opierałem się na tym przykładzie, co wstawiłeś. Gdybym chciał pobrać wszystkie produkty, które spełniają pewne założenia, to bym zrobił coś takiego:

[SQL] pobierz, plaintext 
SELECT * FROM products p, products_values v WHERE p.id=v.id_product AND v.value1 BETWEEN 50 AND 100 AND v.value2 BETWEEN 15 AND 30
[SQL] pobierz, plaintext 

Coś takiego powinno zwrócić wszystkie produkty, których value1 zawiera się w przedziale 50-100 a value2 w przedziale 15-30.

[nospor]

Trzeba zastosować podobne rozwiązanie jak tu:
http://forum.php.pl/index.php?s=&showt...st&p=793421

[sniver]

w serwisie erobay.pl zastosowałem taki oto format:

Kategorie produktów, a do każdej (zaczynając od rodzica - potomkowie dziedziczą parametry) dodałem parametry.

Każdy z nich zadeklarowałem w/g takich wartości jak: checkbox, select i range.

Każde z nich ma na celu dać użytkownikowi pożądany sposób przefiltrowania informacji oraz przy wysłaniu formularza przygotowanie odpowiedniego zapytania do bazy danych.

I tak w przypadku jeśli parametrem jest checkbox wtedy każdy kolejny parametr z tej grupy rozdzielany jest jako OR (tak by dopasować maksymalnie dużo pasujących wyników).
Gdy wybierze ktoś select to wtedy tylko i wyłącznie wybrana wartość jest brana pod uwagę...
Range - czyli zakres, wtedy system wypisze pole w stylu Cena od: [____] do: [____] - i analogicznie dobierze zapytanie które określi zakres (IMG:style_emoticons/default/smile.gif)

Tyle w teorii..

W praktyce wygląda to tak:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `parametr` (
  `PRM_Id` int(11) NOT NULL AUTO_INCREMENT,
  `CAT_Id` int(11) NOT NULL,
  `PRM_Name` varchar(45) NOT NULL,
  `PRM_FormName` varchar(45) NOT NULL,
  `PRM_Type` enum('select','checkbox','range') NOT NULL,
  `PRM_MultiSelect` int(1) NOT NULL DEFAULT '0',
  `PRM_Value` text NOT NULL,
  `PRM_Sort` int(11) NOT NULL DEFAULT '0',
  `PRM_Active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`PRM_Id`),
  KEY `PARAMERTS` (`CAT_Id`,`PRM_Active`),
  KEY `PRM_FormName` (`PRM_FormName`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
[SQL] pobierz, plaintext 

Opis pól:
PRM_Id - id rekordu w tej tabeli
CAT_Id - id kategorii od której zaczyna się ten parametr (wszystkie kategorie dzieci będą dziedziczyć ten parametr)
PRM_Name - nazwa pola widzialna dla użytkownika: np. Kolor
PRM_FormName - nazwa pola w formularzu
PRM_Type - czyli to ten wybór z wspomnianych informacji powyżej...
PRM_MultiSelect - to nie jest potrzebne do tego zagadnienia (czyli można wywalić)
PRM_Value - tak samo (można pominąć)
PRM_Sort - kierunek sortowania (wartość liczbowa)
PRM_Active - wartość 1 jesli parametr aktywny i widoczny, 0 jeśli nieaktywny i niewidoczny


2 tabelą są wartości tych parametrów:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `parametrvalue` (
  `PRV_Id` int(11) NOT NULL AUTO_INCREMENT,
  `PRM_Id` int(11) NOT NULL,
  `PRV_Value` varchar(255) NOT NULL,
  `PRV_Sort` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`PRV_Id`),
  KEY `parametr` (`PRM_Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
[SQL] pobierz, plaintext 

PRV_Id - id rekordu w tej tabeli
PRM_Id - id parametru (z poprzedniej tabeli)
PRV_Value - wartość widoczna dla użytkownika, np. Parametr Kolor, wartości: różowy, zielony, niebieski...
PRV_Sort - ustawienie kolejności sortowania (wartość liczbowa)


i ostatnia tabela z parametrami przypisanymi do samych produktów:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `productsparametr` (
  `PRP_Id` int(11) NOT NULL AUTO_INCREMENT,
  `PRD_Id` int(11) NOT NULL,
  `PRV_Id` int(11) NOT NULL,
  `PRP_Value` varchar(45) NOT NULL,
  PRIMARY KEY (`PRP_Id`),
  KEY `PRODUCT` (`PRD_Id`),
  KEY `VALUE` (`PRV_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
[SQL] pobierz, plaintext 

Opis pól:
PRP_Id - id rekordu
PRD_Id - id produktu
PRV_Id - id wartości (po tym dojdziecie jaki to parametr)
PRP_Value - wartość dla pola range (zakres)

To by było tyle jeśli chodzi o mój układ.
Napisanie samego zapytania chyba nie powinno stanowić problemu, w razie czego go tu wkleje...

Pozdo 600 i niech moc będzie z wami (IMG:style_emoticons/default/blinksmiley.gif)

[lorak110786]

@sniver
Mam bardzo podobny układ tabel. Prawie identyczny. Niemniej właśnie z zapytaniem mam problem. Mógłbyś się podzielić?

[sniver]

Pomijając tabele z bazą produktów, cenami, promocjami itp. duperelami u mnie mniej więcej wygląda to tak:

To zapytanie liczące ilość produktów z wybranymi parametrami (wybrałem je bo jest krótsze, a długość o wiele mniejsza (IMG:style_emoticons/default/smile.gif) )

[SQL] pobierz, plaintext 
SELECT
	ceil(count(0)/10) AS `licz`
 
FROM
	`products` AS `p`						
		JOIN `productsparametr` AS `pp1` ON (`pp1`.`PRD_Id` = `p`.`PRD_Id`)
		JOIN `productsparametr` AS `pp2` ON (`pp2`.`PRD_Id` = `p`.`PRD_Id`)
		JOIN `productsparametr` AS `pp3` ON (`pp3`.`PRD_Id` = `p`.`PRD_Id`)
 
WHERE
	(`p`.`CAT_Id` IN (53))
	AND (`p`.`PRD_Deleted` = 0)
	AND (`p`.`PRD_Active` = 1)
	AND (
		(`pp1`.`PRV_Id` IN (48,49))
		AND (`pp1`.`PRP_Value` BETWEEN '3' AND '10')
	)
	AND (`pp2`.`PRV_Id` = 50)
	AND (`pp3`.`PRV_Id` IN (1,2,3))
[SQL] pobierz, plaintext 

każde podłączenie tabeli za pomocą join jest generowane przez skrypt php, co w konsekwencji buduje zapytanie sql. Zapytanie zostało wykonane ze strony: http://www.erobay.pl/kategorie/53/Biustonosze.html

więc możesz sobie zobaczyć względem parametrów jak sie to składa w całość (IMG:style_emoticons/default/smile.gif)

prawde mówiąc to troche sie napociłem by to wymyśleć i by działało, względem wyszukiwarki i kategorii w głąb. Oto mój sposób - może nie najlepszy i nie najbardziej efektywny ale działa ;]

PS. Nie wykorzystuje parametrów póki co, ale ten sposób napewno działa - poprostu parametry nie są przypisane do produktów (IMG:style_emoticons/default/biggrin.gif)

Ten post edytował sniver 7.10.2010, 13:10:04

[lorak110786]

@sniver
Zrobiłem tak jak u Ciebie. Buduję w ten sposób duże zapytanie. Zapytanie wykonuje się całkiem szybko a sposób jest nader skuteczny i przede wszystkim działa:)

[SQL] pobierz, plaintext 
SELECT products.id_product, products.name, products.id_category, categories.value AS category, (
 
SELECT ROUND( MAX( price ) , 2 ) 
FROM providers_products priceproviders
WHERE products.id_product = priceproviders.id_product
) AS maxprice, (
 
SELECT ROUND( MIN( price ) , 2 ) 
FROM providers_products priceproviders
WHERE products.id_product = priceproviders.id_product
) AS minprice
FROM products products
JOIN treecat AS categories
USING ( id_category ) 
JOIN products_values AS param61
USING ( id_product ) 
JOIN products_values AS param63
USING ( id_product ) 
JOIN products_values AS param64
USING ( id_product ) 
WHERE products.id_category
IN ( 115 ) 
AND (
param61.id_label = '61'
AND param61.value
BETWEEN 100 
AND 500 
)
AND (
param63.id_label = '63'
AND param63.value
BETWEEN 15 
AND 30 
)
AND (
param64.id_label = '64'
AND param64.value
IN ( 0, 1, 3 ) 
)
[SQL] pobierz, plaintext 

Bardzo dziękuję Ci za pomoc. Serdecznie pozdrawiam:)

[sniver]

nie ma za co - w takim razie myślę że ktoś może zamknąc (IMG:style_emoticons/default/haha.gif)