group_concat om meerdere tabellen te combineren

[vmsw]

Al enige tijd ben ik ermee aan het proberen, echter kan ik er niet uitkomen.

Versimpelde situatie is als volgt, voorbeeld met 3 tabellen:

facturen
- id
- factuurnummer

factuurdelen
- id
- omschrijving

factuurregels
- id
- bedrag

Om het samen te vatten zou ik zo efficiënt mogelijk deze gegevens inladen in PHP, zodat ik krijg:

array (id, factuurnummer, factuurdelen => array(
id, omschrijving, factuurregels => array(
array(id, bedrag),
array(id, bedrag)),
id, omschrijving, factuurregels => array(
array(id, bedrag),
array(id, bedrag),
array(id, bedrag)),
id, omschrijving, factuurregels => array(
array(id, bedrag),
array(id, bedrag))));

Op dit moment doe ik het met 3 query's. Dan haal ik eerst alle facturen op, per factuur haal ik de factuurdelen op, per factuurdeel haal ik de factuurregels op. Dat levert enorm veel query's op, en kost bijv. nu bij de giropost 2,5 minuut om te laden.

Sinds kort ken ik ook het idee van GROUP_CONCAT, en vooralsnog lijkt het veelbelovend. Als ik een query doe op de factuurdelen en dan de factuurregels in de GROUP_CONCAT laat ophalen, doet hij er slechts 3 seconden over inclusief exploden door PHP.

Echter ben ik nu al 2 dagen aan het spelen met de GROUP_CONCAT en GROUP BY, echter lukt het me niet om zonder dubbele gegevens e.d. de data uit de database te halen.

Als iemand me opweg kan helpen bovenstaande gegevens zo efficiënt mogelijk op te halen, zou dat heel fijn zijn.

---

Achtergrondinformatie
De meest efficiënte methode om gegevens op te halen wil ik gaan gebruiken in mijn zelfgemaakte entiteiten-systeem waarbij query's automatisch opgebouwd worden. Als bijv. de facturen opgehaald worden, en de onderstaande entiteiten ook nodig zijn, zou ik dat graag met één query allen ophalen. Ik hoop dan ook dat de SQL query simpel is.

Ik ben bekend met bijv. JOINS, echter lukt het me niet om de gegevens niet-dubbel te krijgen. Hiermee zorg ik bijv. op het moment dat het standaard adres (veel relaties hebben meerdere adresgegevens) opgehaald wordt, door een JOIN tussen relaties en adressen WHERE adres.soort=algemeen. Echter zou ik dat willen gaan vervangen door group_concat, want dan heb ik gelijk alles .

[Voor 6% gewijzigd door vmsw op 30-06-2011 02:22]

[Anoniem: 26306]

Ligt het aan mij of zie ik helemaal geen query? Volgens mij moet dit sowieso met een enkele query kunnen. Als dat niet kan, dan rammelt je databasestructuur.

Ik heb nog nooit GROUP_CONCAT gebruikt. Misschien is dat een hint.

[Avalaxy]

In de laatste alinea raakte je me kwijt. Als je gewoon in je WHERE clause opneemt welk type adresgegeven je op wilt halen dan kun je toch prima je complete resultset in 1 query fetchen?

Hoe groot is je database ongeveer? Ik vind 3 seconden voor een query namelijk ook bizar veel tijd, dat zou in enkele milliseconden moeten kunnen.

[thioz]

@vmsw

Wil je echt zelf een ORM gaan maken ? er zijn al zoveel goede bestaande oplossingen hiervoor zoals Propel en Doctrine ... lijkt me redelijk overbodig.

[vmsw]

@thioz: vooralsnog altijd gedaan, en het werkt vrij fijn. Vooral ook omdat ik per entiteit een class heb die allerlei 'persoonlijke' functies bevat. Er is vast veel mogelijk met propel en doctrine, maar eigenlijk gebruik ik liever geen externe classes die wellicht veel overhead hebben, terwijl het ook "basis" kan (ben niet voor niks met assembly begonnen ). Maar ik zal het nog overwegen.

@Avalaxy, de factuurregels (eigenlijk transactieregels) bevat 20k rijen. 3 seconden is inclusief omzetten naar entiteiten overigens.

Ik snap niet helemaal wat ik verkeerd zeg, of wat ik fout doe, want het enige dat ik kan bedenken dat jullie nu bedoelen is het joinen waarbij er per factuurregel één rij komt, waardoor factuurdelen meerdere keren herhaald worden (onder die rijen) en verrekeningen ook herhaald worden. Maar dat zou heel veel extra (loze) data opleveren.

Maar, laat ik het gewoon open houden zoals ik voorheen deed: wat is de meest efficiënte methode om deze 3 gegevens op te halen en in een array te stoppen zoals bovenstaand?

[Voor 5% gewijzigd door vmsw op 30-06-2011 11:10]

[C0rnelis]

Als je dit even wilt ophelderen:

facturen Wat gebruik je voor onderlinge relaties?
- id
- factuurnummer

factuurdelen
- id is dit ook het factuur id? of een factuurnummer?
- omschrijving waarvan is dit een omschrijving? een factuur of factuurregel? of nog weer iets anders?

factuurregels
- id is dit ook het factuur id? of een factuurnummer?
- bedrag

Oftewel, kan je de tabellen en onderlinge relaties iets meer in kaart brengen?

[Remus]

vmsw schreef op donderdag 30 juni 2011 @ 11:01:
Ik snap niet helemaal wat ik verkeerd zeg, of wat ik fout doe, want het enige dat ik kan bedenken dat jullie nu bedoelen is het joinen waarbij er per factuurregel één rij komt, waardoor factuurdelen meerdere keren herhaald worden (onder die rijen) en verrekeningen ook herhaald worden. Maar dat zou heel veel extra (loze) data opleveren.

In je query join je alles aan elkaar en zorg je ervoor dat je alle gegevens hebt om de factuur, factuurdeel, en factuurregel te bouwen, en sorteer de query op factuur, factuurdeel en factuurregel.
Kijk eerst of de factuur dezelfde is als je al hebt, zo niet: creeer een nieuwe factuur
Kijk vervolgens of het factuurdeel dezelfde is als je al hebt, zo niet: creeer een nieuw factuurdeel en hang deze aan de factuur
Creeer vervolgens de factuur regel en hang deze aan je factuurdeel
enzoverder.

Dit is echt heel basaal. De meeste ORM frameworks doen hetzelfde. Deze wijze van werken (inclusief dubbele/herhaalde gegevens in de resultset) is (bijna altijd) goedkoper, dan om een rekenkundige explosie van queries te hebben om te vermijden dat je misschien gegevens dubbel uit de database trekt.

[vmsw]

Mijn fout, het er moest bij het factuurdelen nog een kolom factuurid, bij factuurregels factuurdelenid.
De relatie ligt van vrij voor de hand; het factuurdeel refereert naar factuur.id en factuurregels heeft referentie naar factuurdelen.id

factuurnummer, omschrijving en bedrag zijn puur om 'een veld' te hebben, hebben verder geen betekenis.

@Remus: dat doe ik ook weleens met een JOIN inderdaad, maar eigenlijk vind ik de methode wat minder netjes i.v.m. de hoeveelheid overhead door alle herhalende dingen. Het zou kunnen, maar als je dan bijv. ander gerelateerde dingen gaat halen, zoals producten die gekoppeld aan de facturen, relaties en adressen die ook weer betrokken zijn, krijg je een grote hoeveelheid dubbele regels.

[Voor 25% gewijzigd door vmsw op 30-06-2011 17:35]

[Remus]

vmsw schreef op donderdag 30 juni 2011 @ 14:41:
Mijn fout, het er moest bij het factuurdelen nog een kolom factuurid, bij factuurregels factuurdelenid.
De relatie ligt van vrij voor de hand; het factuurdeel refereert naar factuur.id en factuurregels heeft referentie naar factuurdelen.id

factuurnummer, omschrijving en bedrag zijn puur om 'een veld' te hebben, hebben verder geen betekenis.

@Remus: dat doe ik ook weleens met een JOIN inderdaad, maar eigenlijk vind ik de methode wat minder netjes i.v.m. de hoeveelheid overhead door alle herhalende dingen. Het zou kunnen, maar als je dan bijv. ander gerelateerde dingen gaat halen, zoals producten die gekoppeld aan de facturen, relaties en adressen die ook weer betrokken zijn, krijg je een grote hoeveelheid dubbele regels.

Dan doe je het niet, maar het blijft ondanks de - op het oog - inefficientie over het algemeen een van de meer efficiente manieren van queryen van samenhangende element. Je moet het alleen niet tot een al te grote diepte van je objectstructuur doen (anders loop je de kans dat je uiteindelijk je hele database in één query trekt.

Ik zie overigens niet helemaal in wat er 'niet netjes' aan is, het afvuren van 1 + n*k queries, of het bij elkaar plakken van verschillende velden om die vervolgens in je applicatie weer uit elkaar te trekken is dat zeker wel?

[Voor 4% gewijzigd door Remus op 01-07-2011 10:26]

[vmsw]

Zit alleen nog te zoeken naar logische manier om query op te bouwen, daar er bijv. relaties onder facturen vallen (die weer adressen onderz ich hebben), waarmee ik bijv. tot het volgende kom:

SQL:

SELECT 4_relaties.id, 4_verrekeningen.id, 4_transacties.id, 4_transactieregels.id
FROM 
(((4_verrekeningen) 

LEFT OUTER JOIN 4_transacties ON 4_verrekeningen.id = 4_transacties.verrekening 
LEFT OUTER JOIN 4_verrekeningen_betalingen ON 4_verrekeningen.id=4_verrekeningen_betalingen.verrekening
LEFT OUTER JOIN 4_relaties ON 4_relaties.id=4_verrekeningen.relatie) 

LEFT OUTER JOIN 4_transactieregels ON 4_transacties.id = 4_transactieregels.transactie 
LEFT OUTER JOIN 4_waardebonexemplaren ON 4_transacties.id=4_waardebonexemplaren.transactie
LEFT OUTER JOIN 4_relaties_adressen ON 4_relaties.id=4_relaties_adressen.relatie)

Als ik dynamisch wil opbouwen moet ik dan steeds de onderliggende entiteiten één haakje verder plaatsen, iets wat minder handig is dan als ik het direct in hetzelfde haakje kan opnemen.

[Voor 216% gewijzigd door vmsw op 03-07-2011 18:41]