[MySQL] Left join meerdere rijen

[Currahee]

Momenteel werk ik aan een script die de laatste Tweets van een lijst ophaalt van de Twitter api en deze opslaat in een MySQL database. De lijst bevat zowel tweets als retweets van de geselecteerde personen en worden als volgt opgeslagen:

Tweets: om alle tweets op te slaan. Is de tweet niet van een persoon uit de lijst maar van daarbuiten dan worden de gegevens van de persoon buiten de lijst opgeslagen.

• tweetid
• username
• text
• etc.

Retweets: deze tabel wordt gebruikt om degene die in de lijst staat (en heeft geretweet) te koppelen aan de persoon buiten de lijst (waarvan de tweet opgeslagen ligt in de tabel tweets hierboven). Doordat de lijst over één bepaald onderwerp gaat kan het voorkomen dat een tweet meerdere keren geretweet wordt door verschillende personen uit de lijst. Dit geeft dan meerdere rijen in deze tabel.

• tweetid
• retweet_username

Nu wil ik graag in één query alle tweets en de informatie ophalen van de personen die deze geretweet hebben (niet, door 1 persoon, door meerdere personen). Ik doe dit door middel van onderstaande, voor dit voorbeeld, vereenvoudigde query.

MySQL:

SELECT 
tweets.tweetid,
tweets.username,
retweets.tweetid,
retweets.retweet_username
FROM tweets
LEFT JOIN retweets
ON tweets.tweetid = retweets.tweetid     
                                        

Probleem is nu dat hij een tweet 2 keer teruggeeft als deze 2 keer geretweet is. Graag zou ik zien dat hij één tweet (rij) teruggeeft met daarin de personen die deze geretweet hebben (alleen jan of jan, piet, klaas). Ik heb me helemaal suf gezocht op Google, maar een GROUP by of GROUP_CONCAT zijn volgens mij hier niet de oplossing. Hoe kan ik dit het beste oplossen? Een hint naar een juiste zoekterm zou mij ook al ontzettend helpen!

[P.O. Box]

Volgens mij zul je toch GROUP_CONCAT moeten gebruiken voor dit probleem... alhoewel ik het zelf op zou lossen in de programmeercode en niet in de databasecode...
let op trouwens, GROUP_CONCAT heeft een maxlength (ik geloof van 1000 karakters), bij veel retweets zit je daar al gauw aan... je kunt de maxlength aanpassen, maar ik weet niet of je daar toe gemachtigd bent...

[Currahee]

Bedankt voor de reactie. Met nog wat extra gepruts, wetende dat ik met GROUP_CONCAT op de goede weg zat, is het me gelukt om het voor elkaar te krijgen.

De maxlength kan ik zelf niet aanpassen en bekijk tijdens het testen of dit eventueel problemen gaat opleveren. Gezien het aantal te volgen accounts lijkt me dit echter onwaarschijnlijk.

[DEiE]

Waar P.O. Box op doelt is dat het geen elegante oplossing is om dit in de database op te lossen. In je code moet je vervolgens de string met id's weer uit elkaar trekken en daarbij kan je ten eerste tegen de maxlength aan lopen, maar ook tegen grotere problemen.

Momenteel krijg je, zover ik het zie, alle tweets terug, met of zonder de retweets. Vervolgens knoop je de retweets er in code aan vast. Dit gaat redelijk wanneer de database nog niet zo vol zit, maar stel dat je een hele populaire tweet hebt die een paar duizend keer geretweet wordt. Je haalt dan een grote hoeveelheid tweets op, zonder dat ze relevant zijn. In deze situatie lijkt het me wenselijker om bijvoorbeeld te zien hoe vaak een tweet geretweet is, en wanneer relevant haal je de retweets op om irrelevante informatie en bandbreedte te voorkomen.

Aan de andere kant kan ik me ook voorstellen dat je een chronologisch overzicht wilt maken van de tweets en retweets in het systeem. In dat geval zou ik het omdraaien: laat de retweet leidend zijn, en knoop de tweet aan de retweet in plaats van de retweets aan een tweet te koppelen. Hierbij maak je gebruik van een één-op-veel relatie in plaats van een veel-op-één, wat vanuit code makkelijker te doen is.

Wanneer je zelf tweets en retweets aan elkaar gaat joinen ben je in de meeste gevallen het werk van de database aan het overnemen, terwijl dat juist iets is waar de database goed in is (door middel van efficiente indices, etc.). Laat dit dan ook aan de database over, tenzij je met ORM frameworks, caching of dergelijke praktijken werkt, waar het wél nuttig is, maar in dit geval niet van toepassing lijkt.