Explain Query

[webarto]

Please :)

Ovako imam pitanje u vezi querya, radi se o pretrazi atributa, pretražuju se atributi sa id brojem 6 i 8, queryi rade ali ne kako očekujem...

1.
SELECT * FROM resource_attributes
WHERE
(attribute_value LIKE '%La%' AND attribute_id = 6) AND
(attribute_value LIKE '%Ho%' AND attribute_id = 8)
GROUP BY resource_id


Ovo radi kada se umjesto AND stavi OR, ali onda dobijam djelomičnu pretragu, jer oba uslova (zagrade) moraju biti zadovoljena u slučaju da je OR onda je dovoljno samo jedan. U slučaju da je AND explain vraća Impossible WHERE...

2.
SELECT * FROM resource_attributes
WHERE
(attribute_value LIKE '%La%' OR attribute_value LIKE '%Ho%')
AND attribute_id IN(6,8)
GROUP BY resource_id

Ovo radi OK, ali "La" može biti atribut 8 a mora biti atribut 6 i obrnuto.

Pitanje je kako da atribut 6 bude "La" i atribut 8 bude "Ho" i oba uslova budu zadovoljena inače da ne vrati ništa :)

Riješio sam ovo na drugi način jer ima još komplikacija ali me čisto zanima gdje griješim. Hvala.

Tabela:

http://pokit.etf.ba/get/ba21cbe1b4cd176d61aa7269d6a33862.png

[jablan]

oba uslova (zagrade) moraju biti zadovoljena u slučaju da je OR onda je dovoljno samo jedan.

Kako mogu oba da ti budu zadovoljena? Polje attribute_id može biti ili 6 ili 8, ne može biti i 6 i 8 u isto vreme.

Šta ustvari želiš da dobiješ kao rezultat? Po meni, čini se da ti upravo treba ova prva varijanta sa ((AND) OR (AND)).

[webarto]

Ma ne, znam da ne može i treba da ne može, već ovakva je situacija...

resource_id attribute_id attribute_value
1 6 La Iruela
1 8 Hotel
2 8 Hotel

Potreban mi je query, da mi vrati ovaj resource_id da je 1 ako i samo ako atribut 6 sadrži "La" a atribut 8 sadrži "Ho"... ako recimo postoji atribut 8 sa "Ho" a ne postoji atribut 6 sa "La", query bi trebao da vrati ništa. Nadam se da me razumiješ. Hvala.

[jablan]

A pa ne može to tako onda.

Uradi inner join tabele na samu sebe preko resource_id, gde ćeš u where da staviš da leva strana joina ima 6-La, a desna 8-Ho.

Btw, LIKE 'Ho%' je mnogo brže od LIKE '%Ho%', imaj to u vidu.

[webarto]

:)

Ja sam to ovako bio ali mi "ružno" izgleda pogotovo što se pretražuju još par atributa osim ova 2...


SELECT t1.resource_id FROM resource_attributes AS t1
LEFT JOIN resource_attributes AS t2 ON t1.resource_id = t2.resource_id
LEFT JOIN resource_attributes AS t3 ON t1.resource_id = t3.resource_id
WHERE
(t2.attribute_value LIKE '%La%' AND t2.attribute_id = 6) AND
(t3.attribute_value LIKE '%Ho%' AND t3.attribute_id = 8)
GROUP BY t1.resource_id

Znam za Indexe to sam stavio samo reda radi, napravit ću autocomplete iz postojećih vrijednosti u bazi tako da može biti i = umjesto bilo kakvog LIKE, hvala. Ovaj query sa JOIN + LIKE se izvršava za 0.0002s...

[jablan]

Imaš tu jedan join viška. U suštini, treba ti ovo (pretpostavljam):


SELECT *
FROM resources r
LEFT JOIN resource_attributes a_6 ON r.id = r.resource_id AND a_6.attribute_id = 6
LEFT JOIN resource_attributes a_8 ON r.id = r.resource_id AND a_8.attribute_id = 8
WHERE
a_6.attribute_value LIKE 'La%'
AND a_8.attribute_value LIKE 'Ho%'


Nema potrebe za GROUP BY.

[webarto]

Kako si znao da je tabela resources :D

Malo sam prepravio, to je to, svaka čast.


SELECT r.id
FROM resources r
LEFT JOIN resource_attributes a_6 ON r.id = a_6.resource_id AND a_6.attribute_id = 6
LEFT JOIN resource_attributes a_8 ON r.id = a_8.resource_id AND a_8.attribute_id = 8
WHERE
a_6.attribute_value LIKE 'La%'
AND a_8.attribute_value LIKE 'Ho%'

[jablan]

To je konvencija koju uostalom fura i ActiveRecord (http://guides.rubyonrails.org/association_basics.html) (ORM za Rails). BTW, ako ti zaista treba samo id tog resursa (a ne i ostala polja), nema potrebe da se joinuje sa tom tabelom, nego samo:


SELECT a_6.resource_id
FROM resource_attributes a_6
INNER JOIN resource_attributes a_8 ON a_6.resource_id = a_8.resource_id
WHERE
a_6.attribute_id = 6
AND a_8.attribute_id = 8
AND a_6.attribute_value LIKE 'La%'
AND a_8.attribute_value LIKE 'Ho%'

[dacha]

Please :)
1.
SELECT * FROM resource_attributes
WHERE
(attribute_value LIKE '%La%' AND attribute_id = 6) AND
(attribute_value LIKE '%Ho%' AND attribute_id = 8)
GROUP BY resource_id


Ovo tvoje može da radi, bez višestrukih JOIN-a, baš sa OR, ali da izdvojiš samo one zapise koji imaju obadva uslova zadovoljena.


SELECT *, COUNT(*) as broj_atributa FROM resource_attributes
WHERE
(attribute_value LIKE '%La%' AND attribute_id = 6)
OR
(attribute_value LIKE '%Ho%' AND attribute_id = 8)
GROUP BY resource_id
HAVING broj_atributa = 2

[webarto]

@jablan, dobar taj RoR :D Treba mi još kolona to sam ja stavio ID da ne komplikujem, hvala ti i na drugoj soluciji.

@dacha, dobar fazon, nikad mi ne bi palo na pamet :)

[dacha]

SELECT r.id
FROM resources r
LEFT JOIN resource_attributes a_6 ON r.id = a_6.resource_id AND a_6.attribute_id = 6
LEFT JOIN resource_attributes a_8 ON r.id = a_8.resource_id AND a_8.attribute_id = 8
WHERE
a_6.attribute_value LIKE 'La%'
AND a_8.attribute_value LIKE 'Ho%'


Ako je ovo u pitanju - da su ti potrebni podaci iz tabele resources, a podaci iz tabele resource_attributes služe samo za uslov, onda ovoj tabeli nije mesto u FROM već u WHERE. Ovako bih ja uradio ovo što si ti iznad:


SELECT r.id
FROM resources r
WHERE
2 = ( SELECT COUNT(*) FROM resource_attributes ra
WHERE
ra.resource_id = r.id
AND
( attribute_value LIKE '%La%' AND attribute_id = 6
OR
attribute_value LIKE '%Ho%' AND attribute_id = 8))
Ako tabela resource_attributes nije velika i uslov sadrži samo nekoliko atributa, onda je svejedno kako ćeš uraditi (sa JOIN ili ovako). Ali, junction tabele uglavnom sadrže stotine hiljada zapisa (resources x atributes), pa ako se pretraga vrši po malo više atributa, onda će JOIN napraviti set sa milionima zapisa i performanse će biti lošije.

[jablan]

^ Gornji LEFT JOIN će imati tačno onoliko zapisa koliko postoji u resources tabeli. Plus je potpuno logično rešenje pošto simulira proširenje tabele resources custom atributima.

[dacha]

Ne, nisam mislio u konkretnom slučaju, naveo sam ovo baš zbog logike koju pominješ. Meni je logično (i semantički ispravno) da u FROM idu one tabele iz kojih se uzimaju podaci za konačni set zapisa (i naravno one koje su neophodne za njihovo povezivanje), a onim tabelama čiji podaci služe samo kao uslov logično mesto je u WHERE. Gore sam hteo da kažem da se, stavljanjem ovih tabela u FROM ponekad pravi ogroman set zapisa bez potrebe.

Naravno, meni je ovo logično, ne znači da je tako. :)

[webarto]

Da, LEFT JOIN kako ga je jablan napisao vraća tačno resurse recimo 10 i 12, znači 2 reda, bez GROUP BY, isto kao i ovaj drugi tvoj (bez GROUP BY), za isto vremena jer je tabela mala. Hvala ;)