Nezgodan MySQL upit (za mene)

Ovako.
Imam 3 tabele.
Prva sadrzi sve proizvode “proizvodi”.
Druga sadrzi sve karakteristike koje jedan proizvod moze da ima, "osobine"
Treca tabela “pro_osobine” sadrzi ID za obe tabele iznad (id_pro i id_osobine).
Tih osobina jednog proizvoda moze da bude vise npr 20-ak, li je za jedan proizvod izabrano npr 5, pa u tabeli “pro_osobine” izgleda ovako

id_pro | id_osobine
1            4
1            5
1           15
1           47
1           20

AKo zelim da filtriram proizvode koji imaju osobine (4, 5, 15, 47, 20), ispisace mi taj proizvod 1.
Ono sto je problematicno kako postaviti uslov da se “id_osobine” u upitu navedu kao
(id_osobine = 4 AND id_osobine = 5 AND id_osobine = 15 AND id_osobine = 47 AND id_osobine = 2)
da jedna kolona bude pozvana vise puta u jednoj tabeli, ali sa razlicitim vrednostima.
Znaci, ako su svi gore navedeni uslovi ispunjen tacnije, da se u tabeli “pro_osobine”, nalazi svaka od navedenih id_osobine za jedan prozvod koji u ovom slucaju ima ID 1 dok, ako imamo ovakav uslov i dodam jos jednu ID vrednost u upitu koja ne postoji u tabeli “pro_osobine”, ne prikaze proizvod:
(id_osobine = 4 AND id_osobine = 5 AND id_osobine = 15 AND id_osobine = 47 AND id_osobine = 2 AND id_osobine = 1442)

Moguce je resiti ovo preko INNER JOIN, ali ako neko izabere u filteru svih 20 osobina, bilo bi previse tih JOINA, sto bi sigurno usporilo ispis proizvoda.

SELECT p.*, o.*
        FROM proizvodi p
        INNER JOIN osobine o
        INNER JOIN pro_osobine pf ON (pf.id_pro = p.id AND pf.id_osobine=4)
        INNER JOIN pro_osobine pf1 ON (pf1.id_pro = p.id AND pf1.id_osobine=5)
        INNER JOIN pro_osobine pf2 ON (pf2.id_pro = p.id AND pf2.id_osobine=15)
        INNER JOIN pro_osobine pf3 ON (pf3.id_pro = p.id AND pf3.id_osobine=47)
        INNER JOIN pro_osobine pf4 ON (pf4.id_pro = p.id AND pf4.id_osobine=20)

Treba mi neko bolje resenje za ovu kombinaciju tabela, ili ako imate neki predlog bolje organizacije.

Ne zvuci mi dobro takva struktura gdje dopuštaš vezu (proizvodID, osobinaID) … Za situaciju kada ne postoji osobina za kljuc osobinaID. Da nemaš toga
toga… nebi imao ni problem?

A moze biti da te nisam najbolje razumio…
Djelovi pitanja su mi prilično nejasno napisani.
Na prvu mi nije bilo jasno dali ispituješ postojanje parova (proizvodID, osobinaID)… Ili postojanje osobina pod trazenim osobinaID…

Bozou, hvala ti sto si se javio.
Meni izgleda ovo razumljivo sto sam pisao, ali verovatno jer sam ja pisao :smiley:

Nego da probam da objasnim ovako.
Proizvod 1 ima ove tri osobine: osobinu 1 i osobinu 2 i osobinu 3
Proizvod 2 ima ove tri osobine: osobinu 2 i osobinu 3 i osobinu 4
Proizvod 3 ima ove tri osobine: osobinu 3 i osobinu 4 i osobinu 5

Svaka od tih osobina ima svoj ID koji vuce iz tabele “OSOBINE”, a naravno, svaki proizvod ima svoj ID, koji vuce iz svoje tabele “PROIZVODI”

Ako prvo npr za uslov stavim da mi se prikazu proizvodi koji imaju osobinu 2 i osobinu 3, izlistace mi se 2 proizvoda Proizvod 1 i Proizvod 2, ali nece proizvod 3 jer on nema osobinu 2.

Ako zelim da mi upit izlista sve proizvode koji imaju osobinu 2, osobinu 3 i osobinu 4, premo gore navedenim osobina svakog proizvoda, za zadati uslov, prikazace mi samo proizvod 2.

E sad, kako svakom proizvodu dodeliti vise osobina, a da se kod postavljanje zahteva (prethodne dve recenice), prikazuju zeljeni proizvodi.

Ja sam uveo trecu tabelu “PRO_OSOBIN”, kao sto je u prvom mom postu, ali nisam siguran da je to najbolje resenje.

Nemaš konačnu listu osobina?
Mislim, da li su osobine promenjive u smislu da postoji mogućnost da će kasnije biti dodavane ili uklanjane?

Da, upravo tako, menjace se kasnije.
Sve te osobine imam u bazi i imam deo gde mogu da ih menjam, dodajem nove.
A napravio sam deo da svaku od tih osobina koje se dodeljuju proizvodu putem checkbox, select polja, input polje, e sad mi fali samo deo gde treba da se prikazu na sajtu proizvodi s osobinama iz filtera koje posetilac na sajtu izabere.

Nesto sam izmuvao sad na ovaj nacin:
AND FIND_IN_SET(447, p.osobine_checkbox)
AND FIND_IN_SET(835, p.osobine_select)

Gde se ovi redovi mogu ponavljati onoliko puta koliko stavki posetilac na sajtu izabere u filteru.
osobine_checkbox je kolona tipa TEXT, gde skladistim sve osobine u obliku 443,854,459,499 tacnije, gde su ovi brojevi ID osobina,
a za input polja i select polja, koristim kolonu osobine_select gde vrednosti skladistim u drugacijem obliku ID:VR => 443:23,448:11, gde je ID - id osobine, a VR vrednost select polja ili input polja.

Ne znam kako ce ovo da se pokaze, pa sam zato i trazio ovde najbolje resenje.

Interesantan upit. Našao sam na netu rešenje koje mi se čini interesantnim:

SELECT p.* FROM proizvodi p 
JOIN osobine o ON p.id=o.id_pro WHERE (o.id_osobine) IN (4,5,15,47,20) 
GROUP BY p.id 
HAVING COUNT(DISTINCT o.id_osobine)=5;
1 Like

Nisam uspeo da iskoristim ovaj tvoj predlog :slight_smile:

Cisto za primer sajt sa silnim checkboxovima kojima se filtriraju proizvodi:

Meni licno mocno sve ovo (samo da ne refreshuje stranu pri svakom izboru). Mislim da to ima kod nekretnina.

Ako neko ima slican, ili bolji sajt, s mocnim filterima, neka pusti ovde.

Suština je vrlo jednostavna.
Rekonstruisao sam tvoj primer gore:

--
-- Table structure for table `proizvod`
--

CREATE TABLE IF NOT EXISTS `proizvod` (
  `id` int(11) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `proizvod`
--

INSERT INTO `proizvod` (`id`, `name`) VALUES
(1, 'pr 1'),
(2, 'pr 2'),
(3, 'pr 3');

--
-- Table structure for table `osobine`
--

CREATE TABLE IF NOT EXISTS `osobine` (
  `prid` int(11) NOT NULL,
  `osid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `osobine`
--

INSERT INTO `osobine` (`prid`, `osid`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(2, 3),
(2, 4),
(3, 3),
(3, 4),
(3, 5);

i onda si naveo da tražiš:

za šta je upit:

SELECT p.* FROM proizvod p 
JOIN osobine o ON p.id=o.prid WHERE (o.osid) IN (2,3) 
GROUP BY p.id 
HAVING COUNT(DISTINCT o.osid)=2;

Znači radiš join tabele proivodi sa tabelom osobine i svim vrednostima osobine koje su ti potrebne, grupišeš rezultat po ID proizvoda i iz rezultata izdvajaš samo proizvode koji imaju sve osobine iz željenog skupa (u ovom slučaju dve).

1 Like

Sad vidim da si dobro objasnio i prvi puta…ne znam zašto mi je na mobitelu djelovalo konfuzno.

Uglavnom, ja bi isto išao na to da grupiram rezultate po id_proizvod, te uvjetovao da grupa mora imati count prema broju osobina koje želiš da proizvod ima. …kako je to snisson sročio sa SQL upitom…

@snisson, isprobao sam ovako kako si naveo, vidim da radi to, svaka cast :slight_smile: i hvala ti!
E sad, imam i onaj nesto komplikovaniji slucaj, gde ID osobine, pored toga sto moze da bude u tabeli ‘osobine’, da ta osobina ima i odredjenu vrednost, pa da pretrazuje one proizvode koji za odredjenu osobinu ima odredjenu vrednost, pa da tabela izgleda ovako:

INSERT INTO osobine (prid, osid, vrednost) VALUES
(1, 1, 393),
(1, 2, 48),
(1, 3, 38),
(2, 2, 94),
(2, 3, 84),
(2, 4, 83),
(3, 3, 22),
(3, 4, 28),
(3, 5, 28);

Pa da sada s tvojim upitom izdvoji proizvode koji imaju 2 i 3 osobinu, a istovremeno se od proizvoda ocekuje da osobina 3 ima vrednost 84, kao sto sam naveo u primeru, sto bi rezultiralo da izlista samo jedan proizvod 2, s osobinom 3 cija vrednost iznosi 84 :slight_smile:

Ufff, što si ga zakomplicirao.
Ako sam dobro shvatio, radi se o tri tablice: ARTIKL, ATRIBUT (osobina) i ARTIKL_ATRIBUT (pro_osobina)

Svaki atribut može imati i vrijednost.

Nešto sam slično radio za komponente računala.
Primjer:
Procesor

  • atribut radnog takta
  • atribut interne memorije
  • atribut socketa
  • atribut…

tablica ARTIKL (ARTIKL,…)
tablica ATRIBUT (ATRIBUT,…)
tablica ARTIKL_ATRIBUT (ARTIKL, ATRIBUT, VRIJEDNOST)

Možda ovako nešto:
SELECT a.*
FROM ARTIKL_ATRIBUT aa, ARTIKL a
WHERE aa.ARTIKL = a.ARTIKL
AND aa.ATRIBUT = 3
AND aa.VRIJEDNOST = 84
AND a.ARTIKL IN (SELECT ARTIKL FROM ARTIKL_ATRIBUT WHERE ATRIBUT = 2 )

SELECT a.*
FROM PRO_OSOBINA aa, PROIZVOD a
WHERE aa.ID_PROIZVOD = a.ID_PROIZVOD
AND aa.ID_OSOBINA = 3
AND aa.VREDNOST = 84
AND a.ID_PROIZVOD IN (SELECT ID_PROIZVOD FROM PRO_OSOBINA WHERE ID_OSOBINA = 2 )

Trnac, hvala na odgovoru, hvala i ostalima, sigurno ce nekome znaciti ova tema ako traze neko slicno resenje.
Mozda sam ga ja zakomplikovao pokusavajuci da objasnim, ali to je samo jedan filter koji pravim i za primer sam dao gore link hallo oglasa.
Trnac, hteo sam da izbegnem ove subupite u ovom IN (), jer iz nekog mog sporije iscitava podatke.
Trenutno sam na onom mom resenju s FIND_IN_SET, pa cu videti, ako, kad se nagomilaju podaci, dodje do usporavanja, menjacu nacin citanja.
Za sada toliko.
Ako neko ima jos neki predlog, dobru ideju, moze da napise ovde, bice podsetnik svakome.

Interesantno je da sam ovakav slucaj imao pre par meseci i da sam ga bio resio s vise JOIN-a, ali sam ja skroz zaboravio to.
Ko razume svaki deo upita, svaku funkciju kako i sta radi, sigurno mu to ostaje u secanju mnogo duze :slight_smile:

Evo nešto ako će ti odgovarati:

Prvo, dohvaćanje svih proizvodID-a koji odgovaraju zahthevu. Vrlo slično je onome gore od snisson-a, ali imaš mogućnost definirati vrijednosti osobina:

 $query_get_pID="SELECT p_o.pID FROM p_o 
 join o on p_o.oID=o.oID
 WHERE ((o.oID=1 and o.name='o_1') or (o.oID=2 and o.name='o_2') or (o.oID=3 and o.name='o_3'))
 GROUP BY p_o.pID HAVING COUNT(DISTINCT p_o.oID)=3";

Malo sam se poigrao imenima, tako da je:
o - tablica osobine
p - tablica proizvodi
p_o - tablica veza između proizvoda i osobina.

pID- proizvod ID
oID - osobina ID
o.name - proizvoljno sam stavio atribut osobini…

Sada, kada ti gornji upit dohvaća željene id-ove proizvoda, lako možeš dalje doći do vrijednosti proizvoda, tipa:

$query="SELECT * FROM p WHERE pID in ($query_get_pID)";

ili, ako sve raspišemo:

$query="SELECT * FROM p WHERE pID in (SELECT p_o.pID FROM p_o 
 join o on p_o.oID=o.oID
 WHERE ((o.oID=1 and o.name='o_1') or (o.oID=2 and o.name='o_2') or (o.oID=3 and o.name='o_3'))
 GROUP BY p_o.pID HAVING COUNT(DISTINCT p_o.oID)=3)";

Što se tiče kompliciranja…neznam na što je trnac mislio, ali što se tiče sheme tablica, to ne kompliciraš ako se mene pita. Dobra je struktura…točno kakva treba biti.

Što se tiče pojašnjenja problema, ako je trnac na to mislio…moglo je biti jasnije, hehe. Al kod tih SQL upita, nikada se ne vidi na prvu što je pisac htio reći…

2 Likeova

Bozoou, potrudio si se, nema sta :), hvala ti.
Reci mi, kakvo je tvoje misljenje o ovome sto trenutno koristim FIND_IN_SET?
Pitam, jer bih se trenutno zadrzao na tome, jer je mnogo jednostavnije, ali nisam siguran kako ce se ponasati kad bude par hiljada upisa.

Podupite treba izbegavati jer je ekstremno sporo. U tom slučaju je mnogo bolje raditi View.
Ja imam jednu bazu sa oko 2 M slogova i upit sa podupitom mi je trajao desetak sekundi, dok je sa View palo na malo jače od sekunde.

Ne bih se složio da treba izbjegavati podupite jer nema generalno najboljeg rješenja.
Ovisi o učestalosti pozivanja upita, broju slogova ali i o indeksima i strukturi upita koji su često od presudnog značaja za brzinu izvođenja upita.
2M zapisa u konkretnom slučaju bi značilo da ima 200.000 artikala i da svaki artikl ima u prosjeku 10 atributa.

Ovde je u pitanju 2 M slogova koji se međusobno uparuju.

Podupiti su drastično sporiji od view-a jer je takva implementacija. Na manjim tabelama mogu fino da funkcionišu, ali ako je u pitanju veći broj slogova vreme izvršavanja im drastično raste.

Točno, a ovo će biti manja tablica.

Ma ništa, snisson je zapravo rješio glavninu problema, ja sam samo to malo drugačije upakirao da možeš uvjetovati vrijednost neke osobine.

Što se tiče [quote=“drmko, post:14, topic:29769”]
FIND_IN_SET
[/quote]

…nisam se do sad sreo s time. Ako ti drži vodu, koristi…ako postane sporo, istraživat ćeš gdje šteka. Ako sl. nisi upoznat, imaš upit “EXPLAIN $query” koji ti kaže što i kako baza radi za određeni query. Pa se tamo može vidjeti dali dohvaćanje ide optimiziranim putem dohvaćanja po ključevima, ili se trigira “pretraživanje polja” koje nije poželjno…

Nijesam baš nešto potkovan sa SQL-om …ali svejedno se nisam sreo s time da podupite treba izbjegavati? Jel možeš pojasniti tu tvrdnju da su oni spori?

Dali su spori samim time što postoje…ili zato što je logično da svaki podupit mora napraviti pretragu po nekom ključu?
Ako je ovo drugo, onda mi je prilično logično da će u gornjem slučaju morati postojati pretraga po dva ključa, dok god se kao rezultat trebaju dohvatiti vrijednosti koje su asocirane uz ključ “proizvodID”, s druge strane treba raditi komparaciju sa vrijednostima koje stoje vezane uz ključ “osobinaID”.
Ti u svom upitu nisi radio usporedbu vrijednosti osobine sa željenom vrijednosti…i iz toga razloga si imao jednu manje pretragu s ključem. (Jer ti nije bilo bitno što stoji uz neki “osobinaID”) Sad kada bi proširio svoj upit da omogućiš uposredbu vrijednosti osobine, kako bi to izveo?

S View se također nisam sreo…to iz napisanog zvuči da je druga opcija za podupit. Proguglat ću to… Teško mi je povjerovati da je algoritam baze mogao biti 10x brži za “džabe”. Mogao je, ali svaki pristup mora imati neke svoje prednosti i mane.