[Database] Fotos met meerdere categorieën

[Guillome]

Hallodio
Ik zit met een probleempje.
Ik heb een database waarin foto`s worden opgeslagen (de url naar de echte foto, extra info etc).
Ik wil dat de fotos meerdere categorieën toegekend kunnen krijgen.
Ik heb dat tot nu toe zo opgelost:

TABEL photos
- photo_id
...
TABEL Categories
- categorie_id
...
TABEL photo_cat
- photo_cat_id
- photo_id
- category_id

De tabellen photos en categories spreken voor zich.
In de tabel photo_cat staat bijv dit

1 - 1 - 2
2 - 1 - 3
3 - 2 - 1
4 - 2 - 2
5 - 2 - 8

Dit betekend:
Foto met photo_id 1 heeft 2 categorieën toegekend gekregen, namelijk 2 en 3.
Foto met id 2 heeft 3 cat., namelijk 1, 2 en 8.

Nu is mijn vraag:
Hoe kan ik op een efficiente manier een lijst krijgen met foto`s uit 1 of meerdere categorieën?
Of zit de hele struktuur niet goed inelkaar?

Bij voorbaat dank!

[Infinitive]

Hoe kan ik op een efficiente manier een lijst krijgen met foto`s uit 1 of meerdere categorieën?

Daarvoor zul je moeten "joinen" tussen je drie tabellen. En wel een heel recht-door-zee join zelfs. Zorg er verder voor dat dubbele photo-id's gedelete worden, want die kunnen meerdere malen voorkomen. Verschillende categorieen krijg je voor elkaar door OR's in je where clause.

Ik weet niet of je bekend bent met een join? Anders raat ik je aan om daar eens het een en ander over te lezen.

[ Voor 16% gewijzigd door Infinitive op 04-05-2004 18:26 ]

[Spooksel]

Ik zou in de tabel photos het volgende doen:
- photo_id -> INT
- photo_cat -> INT,INT,INT (etc)
- photo_overige_info

en dan door middel van explode() oid kijken in welke categorieen de foto allemaal valt!

extra:
Hiermee komt de tabel: photo_cat dus te vervallen en hoef je zoals Infinitive zegt alleen nog maar een JOIN te gebruiken om cat info te linken aan de foto.

[ Voor 30% gewijzigd door Spooksel op 04-05-2004 18:27 ]

[cameodski]

Kun je eens een voorbeeldje posten van wat je graag terug zou willen hebben.
Wil je foto's hebben die aan minstens één categorie gekoppeld zijn of wil je per foto alle categorien zien of nog iets anders?

En op je structuur heb ik niets aan te merken. Die zit prima in elkaar.

[Guillome]

Hmm is dit wat je bedoelde infinitive? Ineens doet hij het volgens mij:

code:

  SELECT 
    *
  FROM 
    photos, photo_cat
  WHERE 
    photo_cat.category_id = ?
    AND
    photos.photo_id = photo_cat.photo_id

[Guillome]

cameodski schreef op 04 mei 2004 @ 18:26:
Kun je eens een voorbeeldje posten van wat je graag terug zou willen hebben.
Wil je foto's hebben die aan minstens één categorie gekoppeld zijn of wil je per foto alle categorien zien of nog iets anders?

En op je structuur heb ik niets aan te merken. Die zit prima in elkaar.

Gelukkig zit die goed in elkaar

Maar ik moet ook meerdere categorieen kunnen opgeven.
Dan is dat zeker zoiets?

WHERE
photo_cat.category_id = 3
OR
photo_cat.category_id = 4

?
Iig doet deze code wel wat ik wil (voor iig 1 categorie dan)

code:

  SELECT 
    *
  FROM 
    photos, photo_cat
  WHERE 
    photo_cat.category_id = ?
    AND
    photos.photo_id = photo_cat.photo_id

[ Voor 33% gewijzigd door Guillome op 04-05-2004 18:32 ]

[cameodski]

Spooksel schreef op 04 mei 2004 @ 18:26:
Ik zou in de tabel photos het volgende doen:
- photo_id -> INT
- photo_cat -> INT,INT,INT (etc)
- photo_overige_info

en dan door middel van explode() oid kijken in welke categorieen de foto allemaal valt!

extra:
Hiermee komt de tabel: photo_cat dus te vervallen en hoef je zoals Infinitive zegt alleen nog maar een JOIN te gebruiken om cat info te linken aan de foto.

En als er nu dertig categorien zijn? Dan wordt je datamodel echt heel erg slecht.

XLerator schreef op 04 mei 2004 @ 18:30:
[...]
Maar ik moet ook meerdere categorieen kunnen opgeven.
Dan is dat zeker zoiets?

WHERE
photo_cat.category_id = 3
OR
photo_cat.category_id = 4

Kan makkelijker: WHERE photo_cat.category_id in (3,4)

[ Voor 24% gewijzigd door cameodski op 04-05-2004 18:34 ]

[Guillome]

dat "in" kent hij niet
Ik gebruik mysql met jsp

[cameodski]

XLerator schreef op 04 mei 2004 @ 18:40:
dat "in" kent hij niet
Ik gebruik mysql met jsp

Kent hij dat niet
MySQL heeft toch echt een in operator tbv een list. Ook al voordat subqueries werden ondersteund.

Weet je zeker dat ie IN niet kent? Of zit het probleem ergens anders?

[Guillome]

euh nou.

code:

  SELECT 
    *
  FROM 
    photos, photo_cat
  WHERE 
    photo_cat.category_id = 2
      AND
    photos.photo_id = photo_cat.photo_id

doet ie prima, en

code:

  SELECT 
    *
  FROM 
    photos, photo_cat
  WHERE 
    photo_cat.category_id IN (2,3)
      AND
    photos.photo_id = photo_cat.photo_id

geeft ie een foutmelding.
evt. de url met foutmelding nodig?

[cameodski]

Mmm, ik moet zo weg.
In je startpost staat trouwens dat er in photo_cat een cat_id zit ipv een category_id.

Dit is wat in de manual van MySQL staat over IN.

expr IN (value,...)
Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion.
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1

The number of values in the IN list is only limited by the max_allowed_packet value. To comply with the SQL standard, from MySQL 4.1 on IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. From MySQL 4.1 on, IN() syntax also is used to write certain types of subqueries. See section 14.1.8.3 Subqueries with ANY, IN, and SOME.

[ Voor 84% gewijzigd door cameodski op 04-05-2004 19:14 ]

[Guillome]

das gewoon category_id

[nescafe]

Je photo_cat_id heeft geen primary key photo_cat_id nodig (of wou je hierop sorteren?)

't Is idd wel handig als je aangeeft welke foutmelding je krijgt.

[Guillome]

Voor het bewerken moet dat toch? Die prim. key

De foutmelding:

HTTP ERROR: 500 SELECT * FROM photos, photo_cat WHERE photo_cat.category_id IN (2,3) AND photos.photo_id = photo_cat.photo_id : 0

edit, hoe doe jij dat zo snel?

[ Voor 45% gewijzigd door Guillome op 04-05-2004 20:12 ]

[nescafe]

Oftewel:

HTTP ERROR:
500 SELECT * FROM photos, photo_cat WHERE photo_cat.category_id IN (2,3) AND photos.photo_id = photo_cat.photo_id : 0

Nu ben ik zelf geen jsp-specialist...

[edit]
Hoe bedoel je 'voor het bewerken'?
Je hebt die key niet nodig omdat het veld 'photo_cat_id' geen referenties heeft met andere velden. Bovendien ga je er niet op sorteren en ga je er ook geen selecties op loslaten. M.i. is het hele veld dus overbodig.

[edit]
Ik heb toevallig ergens een scriptje draaien waar ik jouw tekst als querystring ingeef en de 'gewone tekst' terugkrijg

[edit]
Die was flauw.. zal het ff strippen..

http://j.ottenschot.nl/qstring.cgi?geef%20hier%20iets%20in geeft je tekst gedecodeerd terug

[ Voor 71% gewijzigd door nescafe op 04-05-2004 20:30 ]

[Guillome]

handig
En volgens mij kan die idd weg

[cameodski]

nescafe schreef op 04 mei 2004 @ 19:35:
Je photo_cat_id heeft geen primary key photo_cat_id nodig (of wou je hierop sorteren?)

In dit geval is de kolom photo_cat_id inderdaad wat overdone.
Maar in veel gevallen is het toch wel handig om de PK uit één kolom te laten bestaan.
Stel dat je naar deze tabel ook weer een relatie wilt leggen. Dan krijg je dus een PK die uit drie kolommen bestaat en dat kan nog wel gekker worden.
En erg leesbaar wordt het daar ook niet van. Want van aan samengestelde FK's kun je niet intuitief zien aan welke tabel(len) deze gekoppeld is.