[SQL] SELECT Query met een n:m tabel

[Robin]

Ik ben bezig met een soort weblog waarbij er op een artikel reacties achtergelaten kunnen worden.
Nu heb ik een tabel "stories" en een tabel "reactions" beiden met primary keys "id".
Ik heb een koppeltabel gemaakt "stories_has_reactions".

Het probleem is dat ik geen 'juiste' query kan maken waarbij ik reacties kan opvragen bij een story.

Ik had twee manieren bedacht.. beiden waren fout:
SELECT reactions.*
FROM reactions,stories_has_reactions
WHERE reactions.id IN (
SELECT stories_has_reactions.reactions_ID
WHERE stories_has_reactions.stories_ID = '$id'
)
SELECT *
FROM reactions
WHEREid IN (
SELECT reactions_ID
FROM stories_has_reactions
WHERE stories_has_reactions.stories_ID = '$id'
)

Kan iemand me het even uitleggen? Mijn SQL handboek ligt bij mijn ouders thuis :(

Dit is m'n databasemodel: http://www.ninaterbeek.nl/tmp/databasemodel.png

[Voetsjoeba]

Waarom zo ingewikkeld doen via stories_has_reactions ? Ik heb zelf een comment-systeem gebouwd via php en mysql, en deed het zo: het ID field van de reactions tabel is het id van het item uit de tabel 'stories'. Op die manier kun je heel gemakkelijk alle commentaar voor een bepaalde story opvragen:

SELECT * FROM reactions WHERE id = '$id' ORDER BY postdate DESC;

$id is dan het ID van de story in de tabel stories waarvan je de reacties wil opvragen.

[Robin]

Jup, dat werkt :)

Op school heb ik echter geleerd dat je bij een n:m relatie een koppeltabel moet gebruiken anders is het 'bad practice'

Het volgende werkt bij mij ook.. ik weet overigens niet of dit de juiste manier is:
SELECT DISTINCT shr.reactions_ID,shr.stories_ID
FROM reactions AS re,stories_has_reactions AS shr
WHERE shr.stories_ID = '$id'

[Robin]

Waarom zo ingewikkeld doen via stories_has_reactions ? Ik heb zelf een comment-systeem gebouwd via php en mysql, en deed het zo: het ID field van de reactions tabel is het id van het item uit de tabel 'stories'. Op die manier kun je heel gemakkelijk alle commentaar voor een bepaalde story opvragen:

SELECT * FROM reactions WHERE id = '$id' ORDER BY postdate DESC;

$id is dan het ID van de story in de tabel stories waarvan je de reacties wil opvragen.

Op school heb ik geleerd dat wanneer je te maken heb met een n:m relatie je een koppeltabel moet gebruiken.. vandaar dat ik mijn db_model zo gemaakt heb.

Graag zou ik toch nog weten hoe het werkt met een koppeltabel.

[Voetsjoeba]

Wat is in godsnaam een n:m relatie ? Op school leren ze je misschien hoe zij het zien, maar in de praktijk komt het vaak heel anders uit. Het is niet omdat je op school die mogelijkheid hebt gezien dat het daarom per se zo moet. Met die extra tabel belast je je database nog meer als je ooit veel commentaar krijgt, en een 'id' veld heeft commentaar eigenlijk niet nodig, behalve als je commentaar wil gaan nesten. Daarom kun je beter meteen het id van de story gebruiken. Maar goed - ik laat het aan iemand anders over.

[Gerrit55]

SELECT reactions.*
FROM reactions,stories_has_reactions
WHERE reactions.id = stories_has_reactions.id

[Robin]

ol55: Dat is het inderdaad! Bedankt... de uiteindelijke code is dan
SELECT reactions.*
FROM reactions,stories_has_reactions
WHERE reactions.id = stories_has_reactions.reactions_ID AND stories_has_reacions.stories_ID = '$id'

Voetsjoeba: Het is juist belastbaar als je het zonder koppeltabel doet. Hetgeen wat ik rood gemaakt heb is (volgens mij) een INNER JOIN, hiermee moet je eigenlijk werken als je een n:m relatie hebt. n:m is een meer op meer relatie.

Als ik thuis ben zal ik het nog even opzoeken... Bedankt in ieder geval!

[dioneo]

Je structuur is ff anders dan optimaal. Voeg een verwijzing naar je story toe in reactions-tabel;

ALTER TABLE `reactions` ADD `stories_id` INTEGER UNSIGNED;

sla dan je reactie op met het id van de stories

query m vervoglens zo

select
s.id, s.title, s.text, s.postdate,
r.id as reaction_id
r.postdate as reactiondate,
r.name, r.email,r.text as reaction

from stories s
left outer join reactions r on(r.stories_id=s.id)
order by s.postdate desc limit 50

dan krijg je alle 50 laatste postings, en als in een recordset de reactiondate is gezet is het een reactie (kun je dus in je opmaak iets mee doen, dwz het bericht niet tonen, wel de reactie) anders is het een origineel bericht.

wil je alleen weten hoeveel reacties er op een story zijn dan doe je

select s.id, s.title, s.text, s.postdate, count(r.id) as aantalreactions
from stories s, reactions r
where r.stories_id=s.id
group by id, title, text, postdate

[Robin]

Bij nader inzien hebben we het helemaal niet over een n:m relatie.
Een n:m relatie is meer het voorbeeld van 'meerdere docenten geven meerdere leerlingen les' : 1 docent heeft meerdere leerlingen en 1 leerling heeft meerdere docenten...

Hier spreken we gewoon over een 1:n relatie dat betekent dat Voetsjoemba uiteindelijk wel gelijk heeft ;)
1 story heeft meer reactions..
1 reactie heeft NIET meer stories :)

gewoon een foreign key(stories_ID) in de reactionstabel. Alsnog bedankt.. Ik weet nu weer goed wat het verschil is..

[Dauntless]

Voetsjoemba uiteindelijk wel gelijk heeft ;)
Hehe, Voetsjoemba, grappige naam :D ('t is Voetsjoeba ;) ).