[ bogdan.kecman @ 24.11.2008. 14:02 ] @

Citat:

Mister_rap:
Ono sto je meni potrebno je da dobijem sve slike koje imaju tagove: (tag_id) 1 i 4 i 3 i 21

Sta znaci "dobijem sve slike" posto ti se "slika" ne nalazi u bazi koliko ja vidim. Ako pretpostavimo da hoces da dobijes spisak ID-ova slika, za listu tagova onda je to otprilike ovako:
(ovaj upit ti vraca spisak ID slika koje imaju neki od tagova "1, 4, 3 ili 21"

Code:

SELECT DISTINCT picture_tags.picture_id FROM picture_tags WHERE picture_tags.tag_id IN (1, 4, 3, 21);

ako ti nije potreban samo spisak ID-eva nego hoces da povuces i nesto iz pictures tabele (na primer pictures.name) onda mali join:

Code:

SELECT DISTINCT picture_tags.picture_id, pictures.name FROM pictures, picture_tags WHERE pictures.id = picture_tags.picture_id AND picture_tags.tag_id IN (1, 4, 3, 21);

Sto se "pristupa" tice, nije los, samo je "best practice"
- ime entiteta (tabele) je u "jednini"
- ime atributa (polja) je entitet_atribut

tako da bi tabele trebalo da budu:

Code:

create table picture (
  picture_id int,
  gallery_id int,
  picture_name varchar
);

create table picture_tag (
  picture_tag_id int, -- sta je ovo ?
  picture_id int,
  picture_tag_tag_id -- da li vidis da je ovde nesto pogresno ???
)

e sad, kad napravis tabelu sa normalnim imenima, provalis da ti tvoj sadasnji picture_tags_id ne sluzi nicemu !?! jel tako .. cist visak .. ali si stavio da ipak imas PK (sto je good practice) a nisi hteo da koristis tag_id kao PK posto nije unique .. e .. to onda napravis ovako .. dakle, ovako je "ispravno":

Code:

create table picture (
  picture_id int,
  gallery_id int,
  picture_name varchar,
  PRIMARY KEY (picture_id)
);

create table picture_tag (
  picture_tag_id int,
  picture_id int,
  PRIMARY KEY (picture_tag_id, picture_id)
);

savetujem ti da skines MySQL WorkBench .. odlicna je alatka, a pomoci ce ti pri normalizaciji ...

[ bogdan.kecman @ 25.11.2008. 12:23 ] @

ako ti treba id slike koja ima sva 4 taga onda bi morao da radis nesto ovako:

Code:

create table picture_tag (
  picture_tag_id int,
  picture_id int,
  PRIMARY KEY (picture_tag_id, picture_id)
);

INSERT INTO picture_tag (picture_id, picture_tag_id) VALUES (70,1), (70,11), (70,17), (70,4), (70,21), (70,3), (71, 1), (71,4), (71,3), (71,21), (72, 1), (72,5), (72,6);

mysql> SELECT picture_id, GROUP_CONCAT(picture_tag_id ORDER BY picture_tag_id ASC SEPARATOR ',') AS flags FROM picture_tag WHERE picture_tag_id IN (1, 4, 3, 21)  GROUP BY picture_id;
+------------+----------+
| picture_id | flags    |
+------------+----------+
|         70 | 1,3,4,21 |
|         71 | 1,3,4,21 |
|         72 | 1        |
+------------+----------+
3 rows in set (0.01 sec)

-- sada ako hoces id'eve slika koje imaju sva 4 flega:
mysql> SELECT picture_id FROM (SELECT picture_id, GROUP_CONCAT(picture_tag_id ORDER BY picture_tag_id ASC SEPARATOR ',') AS flags FROM picture_tag WHERE picture_tag_id IN (1, 4, 3, 21) GROUP BY picture_id) t WHERE t.flags='1,3,4,21';
+------------+
| picture_id |
+------------+
|         70 |
|         71 |
+------------+
2 rows in set (0.01 sec)

-- ako hoces da imaju samo ta 4 flega i nijedan drugi onda malo drugacije:
mysql> SELECT picture_id, GROUP_CONCAT(picture_tag_id ORDER BY picture_tag_id ASC SEPARATOR ',') AS flags FROM picture_tag GROUP BY picture_id;
+------------+----------------+
| picture_id | flags          |
+------------+----------------+
|         70 | 1,3,4,11,17,21 |
|         71 | 1,3,4,21       |
|         72 | 1,5,6          |
+------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT picture_id FROM (SELECT picture_id, GROUP_CONCAT(picture_tag_id ORDER BY picture_tag_id ASC SEPARATOR ',') AS flags FROM picture_tag GROUP BY picture_id) t WHERE t.flags='1,3,4,21';
+------------+
| picture_id |
+------------+
|         71 |
+------------+

Nadam se da sad resava problem :)

[ Mister_rap @ 25.11.2008. 13:05 ] @

Veliko hvala, radi sigurno ali sam resio drugacije...
Mada je tvoje resenje bolje pa ako budem imao vremena implementiracu ga.

Dodao sam varchar polje all_tags i u njemu stavim zapis 1,3,5,7,8,22,31,
I ako mi treba slika koja ima 1,5,7 tagove

Onda radim select * from tabela where all_tags LIKE '%1,%' and all_tags LIKE '%5,%'...
Pretpostavljam da je znacajno sporije ali radi posao mada nemam pojma kako ce da se ponasa kada tabla bude imala veliki broj zapisa....

[ bogdan.kecman @ 25.11.2008. 21:48 ] @

Ako vrednost za like pocinje sa % to UVEK pravi table scan, dakle, prolazi kroz sva polja u tabeli .. ako tabela ima par desetina redova to je ok, ali ako je malo veca tabela i ako imas par konkurentnih upita to je neverovatno sporo...

imas realno dva ok nacina da implementiras tu pricu ...
1. onako kako sam ti napisao
2. bit polja

verzija 2 ti radi samo ako imas manji broj tagova ... onda generalno imas jedno polje "all_tags" koje je "unsigned bigint" i tagove stavljas kao bitove .. tako da ...

tag1 = 1
tag2 = 2
tag3 = 4
tag4 = 8
tag5 = 16
...
tag50 = 562949953421312
...

i onda samo saberes ... all_tags = 1+4+32+64 ili orujes all_tags = 1|4|32|64
a proveravas da li ima neki tab sa "all_tags & 32 = 32" ili jos bolje proveris da li ima vise njih odjednom sa "all_tags & ( 4 | 8 | 256) = (4 | 8 | 256)"

prva varijanta ti je bolja ako imas "daj mi sve id-ove koji imaju bar ovih 5 tagova" a druga varijanta je odlicna ako imas "daj mi sve id-ove koji imas tacno ovih 5 tagova" posto onda imas prosto poredjenje bez matematike (npr ... WHERE all_tags = 256 | 65536 ...