[SQL] Ingewikkeld probleem
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:
Quote:
- Eric Cartman
- Eric Cartman, Stan Marsh en Kyle Broflovski
- Eric Cartman en anderen
- Eric Cartman, Stan Marsh en anderen
- 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:
Quote:
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?
- 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?
Gewijzigd op 12/06/2010 13:11:16 door Jona than
Gesponsorde koppelingen:
Jonathan DatBenIk op 12/06/2010 13:09:48:
Een klein voorbeeldje van hoe ik deze data wil hebben vind je hier.
Edit:
Hij doet het al weer ....
Gewijzigd op 12/06/2010 13:20:44 door - SanThe -
Alleen wordt ik dan weer totaal gek van het gebruik van de * in de select-clause, benoem de velden waar je wat mee gaat doen!
De enige oplossing is om je "array" tot string te maken binnen de database, en dan wanneer je hem eruit hebt gehaald hem weer uit elkaar te trekken. GROUP_CONCAT kan hierbij helpen. Maar er is hoe dan ook een bewerkingsstap achteraf nodig.
Aanname is dan dat je een hele mooie multidimensionale array wilt opzetten. Maar zolang jij niet toont - met bijvoorbeeld een stukje relevante php code - waar dan jouw probleem ligt, wordt het lastig om je de juiste richting in te trappen.
Note: een GROUPBY_CONCAT heb je niet nodig
Gewijzigd op 12/06/2010 14:02:03 door Noppes Homeland
@Noppes: Dat is (was) het probleem dus: ik had geen idee of dit bestond of mogelijk was, en zo ja, hoe precies. het probleem waar jij om vraagt was dus: ik wist niet hoe ik dit kon doen en had ook geen idee in welke richting ik had moeten zoeken. Waarschijnlijk gaat het wel lukken met het antwoord van Jelmer, maar in ieder geval bedankt voor het meedenken!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
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
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
$bookArray = new array();
$counter = 0;
$currentBook = 0;
foreach($fetch AS $key => $value) {
if($currentBook !== $value['book_id']) {
$counter++;
$bookArray[$counter]['id'] = $value['book_id'];
$bookArray[$counter]['title'] = $value['book_title'];
}
$bookArray[$counter]['authors'][] = $value['author_firstname'].' '.$value['author_lastname'];
$currentBook = $value['book_id'];
}
echo '<pre>';
print_r($bookArray);
echo '</pre>';
?>
$bookArray = new array();
$counter = 0;
$currentBook = 0;
foreach($fetch AS $key => $value) {
if($currentBook !== $value['book_id']) {
$counter++;
$bookArray[$counter]['id'] = $value['book_id'];
$bookArray[$counter]['title'] = $value['book_title'];
}
$bookArray[$counter]['authors'][] = $value['author_firstname'].' '.$value['author_lastname'];
$currentBook = $value['book_id'];
}
echo '<pre>';
print_r($bookArray);
echo '</pre>';
?>
Hiermee krijg je een soortgelijk array als in jouw voorbeeld. (niet getest, maar zoiets zal moeten werken).
Gewijzigd op 12/06/2010 14:27:56 door Arjan -
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...
Wat denken jullie dat de beste optie is?
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.....
Gewijzigd op 12/06/2010 16:03:15 door Arjan -
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
Gewijzigd op 12/06/2010 16:26:04 door Jelmer rrrr
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.
Bedankt voor alle hulp!
Gewijzigd op 12/06/2010 20:04:28 door Jona than
