Using the union methods in database queries/nl

Joomla! 

≥ 3.3

Deze pagina heeft alleen betrekking op Joomla! 3.3 en later.

Het gebruik van UNION in a database-query is een handige manier om het resultaat van twee of meer database SELECT query's, die niet noodzakelijk via een database relatie gekoppeld zijn, te combineren. Het kan ook een nuttige performance optimalisatie zijn. Het gebruik van een UNION om het resultaat van twee verschillende query's te combineren kan soms aanzienlijk sneller zijn dan één enkele query met een WHERE clausule zeker als de query JOINS naar andere grote tabellen heeft.

Voor degenen die bekend zijn met de set theorie doet de UNION precies wat je verwacht; het voegt de set met resultaten uit de ene query samen met de set uit de andere query om zo een set met resultaten samen te stellen wat de verzameling is van de individuele resultaat sets. Als u wilt dat de set wordt gesorteerd, dan moet u bijzondere aandacht besteden aan de manier waarop dat gedaan wordt, zoals later wordt uitgelegd.

De basis

Om een UNION te gebruiken moet u zich bewust zijn van de basis eisen van de SQL-server die u gebruikt. Deze worden niet afgedwongen door Joomla maar, als u ze niet naleeft, krijgt u database fouten. In het bijzonder, elke SELECT query moet hetzelfde aantal velden teruggeven in dezelfde volgorde en met compatibele gegevenstypes om de UNION succesvol te laten zijn.

Een eenvoudig voorbeeld

Stel, u wilt een mailing sturen aan aan groep mensen maar de database is zodanig dat de namen en de e-mailadressen die u wilt versturen niet allemaal in dezelfde tabel zitten. Stel, om een willekeurig voorbeeld te maken, dat u de mail wilt versturen aan alle klanten en alle leveranciers en dat de namen en e-mailadressen, verrassend, respectievelijk zitten in de tabellen customers (klanten) en suppliers (leveranciers).

Deze query haalt alle klant-informatie op die we nodig hebben in de mailing:

$query
    ->select('name, email')
    ->from('customers')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Terwijl deze query hetzelfde doet voor alle leveranciers:

$query
    ->select('name, email')
    ->from('suppliers')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

U kunt het resultaat op deze manier combineren in één enkele query:

$query
    ->select('name, email')
    ->from('customers')
    ->union($q2->select('name , email')->from('suppliers'))
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

De resultaat-set uit de UNION-query zal uiteindelijk iets anders zijn als bij het uitvoeren van de afzonderlijke query's apart omdat de UNION-query automatisch de duplicaten verwijderd. Indien u het niet erg vindt dat de resultaat-set duplicaten bevat (wat wiskundig gezien betekent dat het geen set is), dan zal het gebruik van unionAll in plaats van union de performance verbeteren.

Veel manieren om UNION te gebruiken

De union (en unionAll) methodes zijn heel flexibel in wat ze als argument accepteren. U kunt een "oude stijl" string-query doorgeven, een JDatabaseQuery object, of een array van JDatabaseQuery objecten. Stel dat u bijvoorbeeld drie tabellen heeft, net zoals het voorbeeld hierboven:

$q1->select('name, email')->from('customers');
$q2->select('name, email')->from('suppliers');
$q3->select('name, email')->from('shareholders');

Dan zullen al deze query's hetzelfde resultaat opleveren:

// The union method can be chained.
$q1->union($q2)->union($q3);

// The union method will accept string queries.
$q1->union($q2)->union('SELECT name, email FROM shareholders');

// The union method will accept an array of JDatabaseQuery objects.
$q1->union(array($q2, $q3));

// It doesn't matter which query object is at the "root" of the query.  In this case the actual query that is produced will be different but the result set will be the same.
$q2->union(array($q1, $q3));

union, unionAll en unionDistinct

Er zijn in feite drie UNION methodes beschikbaar.

• union produceert een echte union set van de individuele resultaat-sets; wat betekent dat duplicaten verwijderd zijn. Het proces van elimineren van duplicaten kan wel of niet een performance vermindering veroorzaken, afhankelijk van de betrokken datasets en database structuren.

• unionAll produceert een union van de individuele resultaat-sets maar duplicaten worden niet verwijderd.

• unionDistinct is identiek aan het gedrag van union en is slechts een proxy voor de union methode.

UNION gebruiken in plaats van OR

Er zijn enkele gevallen waar het gebruik van union een aanzienlijke performance verbetering levert in plaats van het meer algemeen gebruikte alternatief van een OR of een IN in een where clausule.

Stel bijvoorbeeld dat u een tabel heeft met producten en u wilt alleen die producten ophalen die tot twee categorieën behoren. Meestal zal dit gecodeerd worden zoals dit:

$query
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catA'), 'or')
    ->where('category = ' . $db->q('catB'))
    ;
$products = $db->setQuery($query)->loadObjectList();

Het is echter waarschijnlijk dat u een aanzienlijke verbetering van de performance ziet bij het gebruik van union:

$query
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catA'))
    ;
$q2
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catB'))
    ;
$query->union($q2);
$products = $db->setQuery($query)->loadObjectList();

U kunt natuurlijk, als u producten uit meer dan slechts twee categorieën wilt selecteren, meer individuele query's combineren.

U zou een vergelijkbare performance verbetering zien door het vervangen van de where clausule door een IN statement.

Resultaten sorteren

Indien u het resultaat gesorteerd wilt hebben moet u weten hoe de database omgaat met de ORDER BY clausules. De volgende opmerkingen gelden voor MySQL maar gelden waarschijnlijk ook voor andere databases.

Stel dat u de namen en e-mailadressen in alfabetische volgorde wilt uitvoeren in bovenstaande mailing voorbeeld. Dan doet u gewoon dit:

$q2
    ->select('name , email')
    ->from('suppliers')
    ;
$query
    ->select('name, email')
    ->from('customers')
    ->union($q2)
    ->order('name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Maar stel dat u om een reden de namen en e-mailadressen wilt uitvoeren in alfabetische volgorde van klant en daarna alle leveranciers. Deze query zal niet het verwachte resultaat geven:

$q2
    ->select('name , email')
    ->from('suppliers')
    ->order('name')
    ;
$query
    ->select('name, email')
    ->from('customers')
    ->order('name')
    ->union($q2)
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Dit is omdat een ORDER BY clausule in de individuele SELECT statements niet zegt over de volgorde waarin de rijen in het uiteindelijke resultaat komen. Een UNION produceert een ongesorteerde set met rijen. Bovenstaande query is syntactisch correct, maar de MySQL-optimalisatie zal eenvoudigweg de ORDER BY clausule negeren op het leveranciers SELECT statement en de ORDER BY clausule op het klanten SELECT statement zal worden toegepast op de uiteindelijke resultaat-set en niet op de individuele resultaat-set.

De manier om dit te omzeilen is een extra kolom toevoegen aan de resultaat-set en daar zodanig op sorteren dat het resultaat de gewenste sortering heeft. Hier is een manier om dat te doen:

$q2
    ->select('name , email, 1 as sort_col')
    ->from('suppliers')
    ;
$query
    ->select('name, email, 2 as sort_col')
    ->from('customers')
    ->union($q2)
    ->order('sort_col, name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Geavanceerde sortering

Echter, er zijn omstandigheden dat het belangrijk is om een ORDER BY clausule op de individuele query's te hebben die niet door de query optimiser worden gedropt. Stel u wilt een speciaal aanbod zenden aan uw 10 top-klanten en uw 5 top-leveranciers. U wilt daarom een LIMIT clausule toepassen in combinatie met de ORDER BY clausule en in dat geval zal de query optimiser de sortering niet negeren. Zo zou u dit kunnen doen:

$q2
    ->select('name , email, 1 as sort_col')
    ->from('suppliers')
    ->order('turnover DESC')
    ->setLimit(5)
    ;
$q1
    ->select('name, email, 2 as sort_col')
    ->from('customers')
    ->order('turnover DESC')
    ->setLimit(10)
    ;
$query
    ->select('name, email, 0 as sort_col')
    ->from('customers')
    ->where('1 = 0')
    ->union($q1)
    ->union($q2)
    ->order('sort_col, name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Het is in dit geval nodig een dummy query te gebruiken omdat anders de order en setLimit methodes uitgevoerd zouden worden op de eind resultaat-set in plaats van de individuele.

Als u fouten ondervindt bij dit voorbeeld, controleer dan het probleem aan de hand van deze gerelateerde patch: 4127