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. Gebruik, 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.

Lots of ways to use union

The union (and unionAll) methods are quite flexible in what they will accept as arguments. You can pass an "old-style" string query, a JDatabaseQuery object, or an array of JDatabaseQuery objects. For example, suppose you have three tables, similar to the example above:

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

Then all of these queries will produce the same results:

// 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

There are actually three union methods available.

• union produces a true set union of the individual result sets; that is, duplicates are removed. The process of eliminating duplicates may or may not incur a performance hit, depending on the data sets and database structures involved.

• unionAll produces a union of the individual result sets but duplicates are not removed.

• unionDistinct is identical in behaviour to union and is merely a proxy for the union method.

UNION gebruiken in plaats van OR

There are some instances where using union can give a significant performance boost instead of the more commonly used alternative of an OR or an IN in a where clause.

For example, suppose you have a table of products and you want to extract just those products that belong to two particular categories. Typically this would be coded something like this:

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

However, it is likely that you will see a useful increase in performance using union instead:

$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();

Of course, if you want to select products from more than just two categories, you can combine more individual queries together.

You should see a similar performance boost when replacing a where clause containing an IN statement.

Resultaten sorteren

If you want your results to be ordered then you need to be aware of how the database deals with ORDER BY clauses. The following comments apply to MySQL but probably apply to other databases too.

Suppose you want to output the names and email addresses in alphabetical order in the mailshot example above. Then you would simply do this:

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

But suppose, for some reason, you wanted to output the names and email addresses in alphabetical order with all customers first, then all suppliers. This query will not give the expected result:

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

This is because an ORDER BY clause in the individual SELECT statements implies nothing about the order in which the rows appear in the final result. A UNION produces an unordered set of rows. The query above will be syntactically correct, but the MySQL optimiser will simply ignore the ORDER BY clause on the suppliers SELECT statement and the ORDER BY clause on the customers SELECT statement will be applied to final result set rather than the individual result set.

The way around this is to add an additional column to the result set and sort on that in such a way that the results will have the desired ordering. Here's one way to do that:

$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

However, there may be occasions when it is important to have an ORDER BY clause on the individual queries that won't be dropped by the query optimiser. Suppose you want to send a special offer to your top 10 customers and your top 5 suppliers. You will therefore want to apply a LIMIT clause in combination with the ORDER BY clause and in that case the query optimiser will not ignore the ordering. This is how you could do it:

$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();

It's necessary to use a dummy query in this case because otherwise the order and setLimit methods would be applied to the final result set rather that the individual one.