[MySQL] meerdere resultaten in JOIN

[Xerohumoris]

Beste Devers,

Voor een website ben ik al een paar keer tegen hetzelfde probleem aangelopen. Tot op dit moment was het op te lossen door in php een tweede query te starten. Maar nu zal ik nog veel vaker een query moeten draaien een dat gaat mij te ver.

Ik zou graag meerdere resultaten uit een JOIN willen binden.

Ik kom dit probleem op een aantal punten tegen. Ik zal de meest complexe voorleggen kijken of jullie daarmee kunnen helpen.

Op de website moet ik columns inlezen. De tabel waar de columns in zijn opgeslagen heeft de volgende structuur. Nog geen indexen dat weet ik maar die zal ik op een later moment er op zetten.

SQL:

CREATE TABLE IF NOT EXISTS `column` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `cat` int(5) NOT NULL,
  `titel` text NOT NULL,
  `column` longblob NOT NULL,
  `datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `afbeelding` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Het kan echter zijn dat er meerdere gebruikers samen één column hebben geschreven. Daarom is er een aparte tabel doe gebruikers aan een column bint.

SQL:

CREATE TABLE IF NOT EXISTS `columnaut` (
  `column` int(11) NOT NULL,
  `gebruiker` int(11) NOT NULL,
  UNIQUE KEY `column` (`column`,`gebruiker`),
  KEY `gebruiker` (`gebruiker`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

columnaut.column = de column.id

Daarnaast moet de voor en achter naam van de gebruiker uit de gebruikerstabel worden gehaald waar columnaut.gebruiker = gebruiker.id

Nu is het probleem dat ik de column uitlees en wil JOINEN op de andere tabbelen. Maar ik wil meerdere resultaten aan gebruikers kunnen binden. Is dit mogelijk. Mooiste zou zijn als het gewoon met 'standaard' query kan.

SQL:

SELECT column.*, gebruiker.voornaam, gebruiker.achternaam, FROM column
JOIN columnaut ON column.id = columnaut.column
JOIN gebruiker on columnaut.gebruiker = gebruiker.id

Dit kan volgens mij niet omdat columnaut meerder resultaten geeft.
Ik zou het resultaat graag als volgt willen zien:

id	titel	column	...	gebruikers
1	Knappende zeepbel	De zeepbel is geknapt	...	(1)Jan Pieters (2)Pieter Jansen

Als dit niet mogelijk is hoor ik het graag. Wil het wel zou ik graag horen hoe ik dat zou moeten doen. Een zoek tocht op google en afspeuren in de mysql docs heeft mij niet echt geholpen.

Ik maak gebruik van PDO. Het is mij al opgevallen dat die soms met wat rare trucjes net iets meer kan dan SQL zelf.

BvD
Daniël

[cytherea]

Ik denk niet dat SQL hiervoor bedoeld is, misschien kun je het voor elkaar krijgen maar handig is het niet. Naar mijn weten kan PDO dit ook niet.

Je kan beter kijken naar bijvoorbeeld een ORM als Doctrine, die kan dat helemaal voor je regelen door middel van een object model en relaties die je beschrijft.

Je zou ook zelf een mapper kunnen schrijven die het resultaat mapt naar meerdere arrays maar dan doe je hetzelfde wat Doctrine al doet.

[pedorus]

Zo'n ORM doet dan zeer waarschijnlijk onder water ook gewoon meerdere queries; wat is daar eigenlijk mis mee? Eventueel kan het wel met group_concat in MySQL.

[Xerohumoris]

pedorus schreef op zaterdag 18 januari 2014 @ 15:00:
Zo'n ORM doet dan zeer waarschijnlijk onder water ook gewoon meerdere queries; wat is daar eigenlijk mis mee? Eventueel kan het wel met group_concat in MySQL.

In de basis is er niet echt veel mis met meerdere queries. Probleem is echter dat het niet gaat om extra query maar het al snel naar de 100 extra queries gaat. Daarom zou ik het graag willen beperken tot één. Dat is zeker sneller.

[Orion84]

Hoezo 100 extra queries? Voor elke column heb je 1 query om de column uit je DB te trekken en 1 query om de gerelateerde gebruikers op te vissen.

[Xerohumoris]

@Orion84: Dat klopt helemaal. Ik wil echter ook een overzicht van alle columns willen geven. Het selecteren van alle columns is niet het probleem. Maar ik moet dan dus voor elke column ook de gebruikers ophalen. En dan zit je wel snel aan enkele 100 extra queries.

En de query die ik eerder gaf is daar niet geschikt voor. Omdat ik namelijk alle informatie per gebruiker per column ga ophalen. Bij twee gebruikers krijg ik de column dus tweemaal terug.

En als ik ga groupen op column.id krijg ik slechts 1 auteur.

[ Voor 6% gewijzigd door Xerohumoris op 18-01-2014 16:45 ]

[Orion84]

Ja, dat bedacht ik mezelf inmiddels ook, dat het je ging om het tonen van een overzicht van meerdere columns en daarbij ook de gebruikers.

Heb je al eens geprobeerd of je database daar moeite mee heeft? Ergens heb ik zo het vermoeden (maar wellicht zit ik er compleet naast), dat je DB liever een flinke stapel hele simpele queries krijgt, dan 1 hele ingewikkelde.

Alternatief is natuurlijk om het gewoon met dubbele columns uit de DB te trekken en in de code die het database resultaat omzet in de inhoud van je webpagina dat op de juiste manier te verwerken.

[ Voor 21% gewijzigd door Orion84 op 18-01-2014 16:50 ]

[Xerohumoris]

Ik kan momenteel helaas niet testen op een echte database. Het gaat namelijk om een complete aanpassing van de tabel waar nu de columns is worden opgeslagen. Die zijn namelijk nu beperkt tot één gebruiker en die staat gewoon op naam in de tabel. (ontwerp fout)

Maar het zou idd best eens kunnen zijn dat meerdere kleine queries (alleen een JOIN op de gebruikers tebal met die van de auteurs) beter is dan een complexe met meerder uitkomsten. Ik denk echter dat het voor de latere verwerking ook makkelijker is.

Probleem is alleen dat de onze hoster ook interne data meerekende in het data verbruik. Dus de overhead is stukken groter, en dus meer data verkeer. (we krijgen overigens wel erg veel data verkeer als je dat vergelijkt met andere hosters)

[CH4OS]

Dan zou ik een extra koppeltabel maken en daarin de users die gewerkt hebben aan een column groeperen.

[Xerohumoris]

Wat is daar anders aan dan ik nu doe? Ik wil namelijk wel elke gebruiker apart houden zodat door geklikt kan worden naar een gebruikers eigen profiel.

[CH4OS]

Waarom zou je met een koppeltabel zoiets kwijt raken dan?

Wat je dan dus in feite doet, is een column relateren aan een groep, die op zijn beurt weer is gerelateerd aan specifieke users binnen die groep.

[ Voor 132% gewijzigd door CH4OS op 18-01-2014 17:04 ]

[Xerohumoris]

Ik maak nu toch ook gebruik van een koppeltabel? Of is dat iets anders?

CptChaos zou je dan een voorstel willen doen voor zo'n tabel?

[CH4OS]

Je hebt nu twee tabellen, met mijn voorstel zou je drie tabellen hebben.
Eén tabel met columns, één tabel met users en één koppeltabel waarin je een relatie legt tussen de column en de users. Mooi zoekwoord in deze: normalisatie.

EDIT:
Ik zei het net verkeerd, dit is wat ik bedoelde.

[ Voor 24% gewijzigd door CH4OS op 18-01-2014 17:10 ]

[CurlyMo]

Hij heeft al drie tabellen, maar de gebruiker tabel niet getoond:

SQL:

SELECT column.*, gebruiker.voornaam, gebruiker.achternaam, FROM column
JOIN columnaut ON column.id = columnaut.column
JOIN gebruiker on columnaut.gebruiker = gebruiker.id

1. column
2. columnaut
3. gebruiker

[Xerohumoris]

Ik heb idd de gebruikers tabel niet laten zien mijn excuus.

Maar zelfs met een koppeltabel lukt het dus niet. Ik ben dus bang dat het niet anders kan dan meerdere queries uitvoeren.

[CH4OS]

Wellicht kan een subquery wat je zoekt? Al heb ik daar zelf geen ervaring mee. Ook de tip met group_concat is nog niet naar gekeken zie ik.

Ik zou in ieder geval de koppeltabel niet weg doen, daarmee behoudt je de structuur en de relaties met de columns en de gebruikers.

[ Voor 78% gewijzigd door CH4OS op 18-01-2014 17:19 ]

[Xerohumoris]

Een subquery gaat zeker niet werken. Als je een subquery hebt met meer dan 1 resultaat gaat het al mis. Dan krijg je al een foutmelding.

Ik kan het fout hebben hoor. Maar als ik een subquery maak die meer dan 1 resultaat geeft krijg ik een error. Code weet ik zo niet meer.

[CurlyMo]

Je query werkte toch prima?

SQL:

SELECT `column`.*, gebruiker.voornaam, gebruiker.achternaam FROM `column`
JOIN columnaut ON `column`.id = columnaut.`column`
JOIN gebruiker on columnaut.gebruiker = gebruiker.id

Kolommen:
1. id
2. cat
3. titel
4. column
5. datum
6. afbeelding
7. voornaam
8. achternaam (NULL)

code:

1   1   Test    [BLOB - 0 B]    2014-01-18 17:14:25         Aap     NULL
1   1   Test    [BLOB - 0 B]    2014-01-18 17:14:25         Noot    NULL
2   2   Test1   [BLOB - 0 B]    2014-01-18 17:14:25         Mies    NULL

[ Voor 11% gewijzigd door CurlyMo op 18-01-2014 17:21 ]

[CH4OS]

Ik heb zo het idee, dat de TS echter als resultaat het volgende wil:

code:

1     1     Test    [BLOB - 0 B]    2014-01-18 17:14:25         Aap Noot    NULL

[itons]

SQL:

SELECT column.*
,  group_concat(voornaam separator(', '))
FROM column
,    gebruiker
,    columnaut
WHERE column.id = columnaut.column 
AND   columnaut.gebruiker = gebruiker.id

dan moet je toch gewoon zoeits doen? kan het ff niet uitproberen

[Xerohumoris]

Er zit ook niet een fout in de query. Het is alleen niet het resultaat wat ik wens.

Deze uitkomst is namelijk erg lastig te bewerken naar een bruikbaar resultaat.

Ik wil elke column namelijk 1 keer hebben, nu krijg je 1 per gebruiker, met meerdere gebruikers. Als je er een array van zou maken krijg je dit:

PHP:

array(
id = 1
titel = "Test"
column = "Leuke test"
datum = "nu"
auteurs = array(
  0 = array(
      id = 1
      voornaam = "Jan"
      achternaam = "Pieters")
  1 = array(
    id = 2
    voornaam = "Pieter"
    achternaam = "Jansen")))

Beetje anders mag ook maar dit is het idee

@itons: GROUP_CONCAT() Zou een optie zijn. Ik heb het alleen nog nooit werkend gekregen. En dat is ook niet mogelijk als je meerdere resultaten wil hebben uit een tabel.

[ Voor 12% gewijzigd door Xerohumoris op 18-01-2014 17:31 ]

[CurlyMo]

SQL:

SELECT 
    `column`.*, 
    GROUP_CONCAT(CONCAT(gebruiker.voornaam, ' ', gebruiker.achternaam)) FROM `column`
JOIN 
    columnaut 
ON 
    `column`.id = columnaut.`column`
JOIN 
    gebruiker 
ON
    columnaut.gebruiker = gebruiker.id 
GROUP BY 
    `column`.id

Showing rows 0 - 1 ( 2 total, Query took 0.0016 sec)

code:

1   1   Test    [BLOB - 0 B]    2014-01-18 17:14:25         Aap Jansen,Noot De Vries
2   2   Test1   [BLOB - 0 B]    2014-01-18 17:14:25         Mies Klaassen

Hetzelfde resulaat maar dan met een subquery:

SQL:

SELECT 
    c.*, 
    (SELECT 
        GROUP_CONCAT(voornaam, ' ', achternaam) 
    FROM 
        gebruiker AS a, columnaut AS b 
    WHERE 
        b.gebruiker = a.id AND c.id = b.`column`) AS gebruiker 
FROM 
    `column` AS c

Showing rows 0 - 1 ( 2 total, Query took 0.0006 sec)

Voordeel van postgresql is dat die makkelijker subqueries kan omzetten naar arrays.

[ Voor 31% gewijzigd door CurlyMo op 18-01-2014 17:57 ]

[CH4OS]

Al is het 'micro-ing', die met de subquery is sneller. Vraag me stiekempjes af hoe dat dan precies komt.

[ Voor 30% gewijzigd door CH4OS op 18-01-2014 18:58 ]

[Hydra]

Dit soort dingen los je dus normaal gewoon in je applicatie op. Dan boeit het niet als je query meerdere rows per column geeft voor 't overzicht; die groepeer je dan gewoon in je applicatie. SQL is geen tool waarmee je perse elk probleem op moet lossen.

[CurlyMo]

Ik zou ook gewoon voor de eerste join gaan en de rest in je applicatie doen.

[Xerohumoris]

Ik denk dat ik toch ook maar ga kiezen om het in meerdere queries te doen. Ik denk namelijk dat dat uiteindelijk het snelste zal zijn.

Ik wil jullie allemaal toch bedanken voor de hulp.

[CurlyMo]

@hydra en ik zeggen juist dat je de join zou moeten gebruiken. Dat is het snelst en makkelijkst. Het is aan jou.

[ Voor 7% gewijzigd door CurlyMo op 18-01-2014 22:54 ]

[SPee]

Een ORM zal soms ook een query uitvoeren waarbij de eerste kolommen de resultaten herhaalt zijn en alleen het laatste gedeelte uniek zijn. De ORM maakt daar dan de unieke resultaten.
Dus heb je maar 2 queries nodig.

SQL:

SELECT columnaut.column, gebruiker.voornaam, gebruiker.achternaam
FROM columnaut 
JOIN gebruiker on columnaut.gebruiker = gebruiker.id

Heb je al eens geprobeerd of je database daar moeite mee heeft? Ergens heb ik zo het vermoeden (maar wellicht zit ik er compleet naast), dat je DB liever een flinke stapel hele simpele queries krijgt, dan 1 hele ingewikkelde.

Vele kleintjes maken 1 grote. Wat je vergeet is dat voor elke query een roundtrip en wachttijd is. Hoe meer losse queries, hoe langer het duurt.

Het is vervelend dat je hoster ook het interne verkeer rekent. Dan is het altijd kiezen tussen 2 kwaden. Maar wat is goedkoper: jouw tijd of het dataverkeer (of een andere hoster)

[krvabo]

CurlyMo schreef op zaterdag 18 januari 2014 @ 22:53:
@hydra en ik zeggen juist dat je de join zou moeten gebruiken. Dat is het snelst en makkelijkst. Het is aan jou.

Group_concat is heel leuk, totdat je die gebruikersnamen weer klikbaar wilt maken.

@TS; how about het volgende:

- Haal alle columns op die je nodig hebt / wil tonen, en itereer / foreach over je resultset waarbij je alle column id's opslaat in een tijdelijke array.
- Pak een tweede query:

SQL:

SELECT gebruiker.id, gebruiker.naam 
FROM columnaut 
INNER JOIN gebruiker ON columnaut.gebruiker = gebruiker.id
WHERE columnaut.gebruiker IN (<array implode van id's>)

Als je het dan handig wil hebben doe je iets als het volgende:

PHP:

$aResult = ...;
foreach ($aResult as $aRow) {
  $aColumnautsById[ $aRow['id'] ] = $aRow['naam'];
}

Dan kun je de namen van de columnisten bij je weergave heel makkelijk ophalen zonder elke keer over de hele array te moeten loopen.
Deze manier is uitstekend te gebruiken bij veel gegevens.

(Ik zie dat SPee hetzelfde idee heeft)

[Xerohumoris]

@krvabo:

Dit is idd iets wat ik heb overwogen. Door eerste een selectie te doen van de columns en dan vervolgens alle auteurs in één keer op te halen. Op andere plekken doe ik dat ook. Ik zou het dan namelijk tot 3 queries kunnen bepreken.

Ik zal het in het achterhoofd houden als optie. Ik ga straks eerst kijken of deze oplossing snel genoeg is. Mocht het toch te lang duren zal ik het terug brengen naar 3 queries en die in PHP gaan koppelen.

[CurlyMo]

Koppelen van informatie doe je doorgaans in je database. Presenteren en verwerken van informatie in je code. Daar werkte je originele join prima voor.

PHP:

header('Content-Type: text/html; charset=utf-8');

$connect = mysql_connect('127.0.0.1', 'xxx', 'xxx');
$db = mysql_select_db('xxx');

$query = mysql_query("SELECT `column`.*, gebruiker.voornaam, gebruiker.achternaam FROM `column`
JOIN columnaut ON `column`.id = columnaut.`column`
JOIN gebruiker on columnaut.gebruiker = gebruiker.id ORDER BY `column`.id") or die(mysql_error());

$i = 0;
$x = 0;
$list = Array();
while($data = mysql_fetch_assoc($query)) {
    if($i != $data['id']) {
        $i = $data['id'];
        $x = 0;
        $list[$i]['titel'] = $data['titel'];
    }
    $list[$i]['gebrukers'][$x]['voornaam'] = $data['voornaam'];
    $list[$i]['gebrukers'][$x]['achternaam'] = $data['achternaam'];
    $x++;
}
print_r($list);

code:

Array
(
    [1] => Array
        (
            [titel] => Test
            [gebrukers] => Array
                (
                    [0] => Array
                        (
                            [voornaam] => Aap
                            [achternaam] => Jansen
                        )
                    [1] => Array
                        (
                            [voornaam] => Noot
                            [achternaam] => De Vries
                        )
                )
        )
    [2] => Array
        (
            [titel] => Test1
            [gebrukers] => Array
                (
                    [0] => Array
                        (
                            [voornaam] => Mies
                            [achternaam] => Klaassen
                        )
                )
        )
)

[Rotterdammertje]

@CurlyMo: wel even sorteren op id, natuurlijk.