Door
- -
op 12-06-2010 13:09
gewijzigd op 12-06-2010 13:11
1.299 views
Momenteel ben ik bezig met een webshop waarin boeken verkocht gaan worden. Het systeem wordt met de hand gebouwd en is dus volledig op maat ontworpen om alles zo optimaal mogelijk te maken voor boeken.
De moeilijkheid zit hem nu in het veld met de auteur(s) van een boek. Het zit namelijk zo: een boek kan één of meerdere auteurs hebben. Indien er meerdere auteurs zijn, wordt er rekening gehouden met de volgorde (de eerstgenoemde auteur is vaak de 'belangrijkste' auteur). Tot slot kan het nog zijn dat er erg veel mensen mee hebben gewerkt die niet allemaal los genoemd worden, dan wordt achter het gewone rijte van (de) auteur(s) de tekst "en anderen" toegevoegd.
Om het wat minder abstract te maken, even een voorbeeldje van een aantal mogelijke opties:
- Eric Cartman
- Eric Cartman, Stan Marsh en Kyle Broflovski
- Eric Cartman en anderen
- Eric Cartman, Stan Marsh en anderen
Voor de opslag van deze data heb ik voor de volgende opbouw gekozen:
book
- id
- author-others*
book_author
- book_id
- author_id
- number**
author
- id
- first-name
- last-name
* Boolean voor de suffix "en anderen".
** Nummer van de auteur bij een bepaald boek, om de volgorde te bepalen. Iemand toevallig een idee voor een betere naam?
Het probleem zit hem nu in het ophalen van de data: hoe kan ik een lijst met boeken én bijbehorende auteurs (in een array) ophalen? Misschien lukt het met trucjes nog wel om de auteurs al door MySQL te formatteren tot de uiteindelijke string, maar dat is absoluut niet de bedoeling. Ik wil dat ik gewoon een array met de auteurs (id, first-name, last-name en number) krijg en die op welke manier dan ook kan verwerken. Een klein voorbeeldje van hoe ik deze data wil hebben vind je hier.
Mijn vraag is nu dus: hoe kan ik dit zo handig mogelijk doen? Ik vrees dat dit niet gaat lukken met één query, dus moet ik nu per boek nog een query uitvoeren om de auteurs op de halen? Of hebben jullie misschien goede ideeën hierover?
Er van uitgaande dat elk boek minimaal één auteur heeft kan je ook het volgende doen:
SELECT
book.id AS book_id,
book.title AS book_title,
author.first-name AS author_firstname,
author.last-name AS author_lastname
FROM
book_author
INNER JOIN
author
ON
author.id = book_author.author_id
INNER JOIN
book
ON
book.id = book_author.book_id
ORDER BY
book_author.book_id ASC
@Arjen: Dat is inderdaad ook nog een optie en nu begin ik te twijfelen wat beter is (Jelmers GROUP_CONCAT of jouw PHP-oplossing). Beide oplossingen hebben extra gedoe in zowel de query als erna in PHP staan.
Het verschil lijkt mij dan: Jelmers optie is sneller, je hebt namelijk veel minder rijen die verstuurd moeten worden en daarna verwerkt worden. Nadeel is wel dat het een beetje onbetrouwbaar is omdat je meerdere waarden achter elkaar plakt, dus weet je nooit of alles goed gaat. Jouw optie heeft die onbetrouwbaarheid niet, maar lijkt me wel weer een stuk langzamer...
Als jij wil dat je volledige controle hebt over je weergave van de auteurs kan je beter voor mijn optie kiezen, omdat je dan alle gegevens van de auteurs (zoals id en naam) apart kunt opvragen en weergeven.
Maar je moet het vanzelfsprekend zelf bepalen. En zoveel langzamer is mijn methode niet hoor. Een foreach loop heeft php zo doorlopen.
En persoonlijk ben ik van mening dat je ook té veel op de performance kan letten terwijl het verschil in tijd vaak minimaal is en de bezoeker het niet merkt.
Een group_concat geeft een string terug. Wanneer je hier iets mee wilt doen dan zal je al snel uitkomen op het exploden van die string.....
Als je GROUP_CONCAT en CONCAT combineert, kan je waarschijnlijk wel meerdere kolommen eruit halen. Of gewoon meerdere GROUP_CONCAT kolommen, voor ieder veld een, en dan later die weer bij elkaar zoeken.
Jonathan, wat weet je nu al? Hoeveel boeken haal je gemiddeld op, en hoeveel auteurs heeft ieder boek gemiddeld? Ik vraag me af of die GROUP_CONCAT functies wel in de query cache blijven hangen. Zo niet, dan is de query met veel joins waarschijnlijk gemiddeld sneller. Maar als die idioot veel rows oplevert, dan is daar ook wel wat tegen te zeggen.
Beste methode: Allebei de queries maken, en testen. #opendeur
Ik heb gekozen voor de manier van Jelmer waarbij alles ge-CONCAT wordt. Om de verwerking in PHP wat eenvoudiger te maken en dus niet met de hand allemaal explode-trucjes te moeten doen, heb ik gezorgd dat de ge-CONCAT-te kolommen in JSON-formaat zijn en dus zonder veel moeite om te zetten zijn naar een array.
In verband met de grootte en overzichtelijkheid van de uiteindelijke code moet je even doorklikken en staat het niet hier. Graag ontvang ik suggesties als die er zijn ;-)
Dan nog over de snelheid. Ik heb nu als test zes boeken in de database gezet, één met twee auteurs en de rest met één auteur (zo'n verhouding is al vrij hoog, in de echte shop verwacht ik dat de verhouding lager ligt). Als ik nu een gewone SELECT doe (zonder de auteurs erbij) duurt dat gemiddeld 24ms, als ik de VIEW gebruik en dus ook de auteurs erbij neem duurt het gemiddeld 28ms. Het duurt dus zo'n 4ms langer, omgerekend zo'n 17% langer. De methode met een 'simpele' JOIN waarbij PHP de auteurs samenvoegt duurt 26ms, waardoor het nettoverschil uitkomt op 2ms of 8%. Voor mij dus zeer acceptabel.
