Joomla 使用Union方法

使用Union方法來查詢可以將兩個沒有實際關(guān)聯(lián)的數(shù)據(jù)表的查詢結(jié)果集進行合并。使用UNION組合不同查詢的結(jié)果有時可能比使用WHERE子句的單個查詢快得多，尤其是當查詢涉及到其他大表的聯(lián)接時。

使用基礎(chǔ)

為了使用union方法，你需要確認你的SQL服務器是否支持。這些并不是Joomla強制要求的，但是如果你的SQL服務器不支持，那么執(zhí)行包含union語法的SQL時，就會拋出數(shù)據(jù)庫錯誤。另外，為了確保union執(zhí)行成功，每個SELECT查詢必須以相同的順序和兼容的數(shù)據(jù)類型返回相同數(shù)量的字段。

簡單的實例

假設你有兩張表，一張是客戶表(#__zmax_customers),存儲了客戶的信息，一張是供應商表（#__zmax_suppliers） 存儲了供應商的信息。現(xiàn)在你需要同時給所有的客戶和供應商發(fā)郵件。

獲得客戶的郵件列表SQL：

$db = JFactory::getDBO();
$query =$db->getQuery(true);
$query->select('name, email')->from('#__zmax_customers')    ;
$mailList =$db->setQuery($query)->loadObjectList();

獲得供應商的郵件列表SQL:

$db = JFactory::getDBO();
$query =$db->getQuery(true);
$query->select('name, email')->from('#__zmax_suppliers')    ;
$mailList =$db->setQuery($query)->loadObjectList();

在這種情況下，你就可以使用Union方法來合并這兩個查詢了。代碼如下：

$db = JFactory::getDbo();
$query=$db->getQuery(true);
$query2 =$db->getQuery(true);
$query->select('name, email')->from('customers')
    ->union($query2->select('name , email')->from('suppliers'));
$mailList =$db->setQuery($query)->loadObjectList();

使用Union的查詢結(jié)果就會將上面兩個查詢的結(jié)果進行合并，并且會移除掉重復的行。如果你不需要移除重復的行，那么可以使用unionAll 來代替union.

多種方法使用Union

在Joomla中，union(unionAll) 方法支持多種方式來使用，可以使用SQL字符串，也可以使用JDatabaseQuery對象或者使用數(shù)組。假設我們有3個不同的查詢，代碼如下：

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

下面的查詢都會返回相同的結(jié)果：

// 使用JDatabseQuery對象作為參數(shù).
$q1->union($q2)->union($q3);
 
// 使用JDatabaseQuery對象和字符串作為出納室
$q1->union($q2)->union('SELECT name, email FROM shareholders');
 // 使用數(shù)組作為參數(shù)
$q1->union(array($q2,$q3));
 
// union查詢和主查詢的順序無關(guān)，雖然我們這里使用q2作為主查詢，但返回結(jié)果和之前是一樣的
$q2->union(array($q1,$q3));

union ,unionAll 和unionDistinct

Joomla對SQL中UNION關(guān)鍵詞提交了3個方法：

• union 產(chǎn)生沒有重復記錄的結(jié)果集。在移除重復的結(jié)果集的過程中是否會產(chǎn)生性能的下降這個數(shù)據(jù)庫的數(shù)據(jù)以及數(shù)據(jù)庫的結(jié)構(gòu)有關(guān)。
• unionAll 產(chǎn)生所有的記錄的結(jié)果集，允許重復
• unionDistinct 和 union一樣。是union的別名。

使用UNION代替OR

在一些情況下，使用union替代 where in  或者 where or 能夠顯著的提高性能。

假設現(xiàn)在你有一個產(chǎn)品表，現(xiàn)在你想獲得兩個特定類別中的產(chǎn)品。通過代碼如下：

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

此時，你使用Union來實現(xiàn)，將會提高查詢的性能：

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

當然，不僅僅是兩個分類，如果多個分類，你也可以union多個。

給結(jié)果集排序

如果需要對結(jié)果集進行排序，則需要了解當前的SQL服務器是如果處理ORDER BY的。下面的代碼在MYSQL上運行成功，但不保證在其他的數(shù)據(jù)庫上能運行成功。

假設現(xiàn)在你想對輸出的結(jié)果集按照name字段進行排序，帶么如下：

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

假設你想先對customer表的name排序，然后再對suppliers表的name排序，那么下面的SQL語句就不會按照你的希望返回結(jié)果：

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

這是因為單個的SELECT 中使用 ORDER BY 不能對最終的查詢結(jié)果進行排序， union查詢將會返回一個無序的結(jié)果集，上面的查詢語句沒有語法錯誤，但是MYSQL的優(yōu)化器會忽略掉suppliers的 order By.而直接使用customers的order by為最終的結(jié)果排序。

解決問題的方法是增加一個用于排序的字段。代碼如下：

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

更加復雜的排序

在某些情況下，我們需要使用order by 對單個select進行排序，且不允許優(yōu)化器忽略。

假設您想向前10名客戶和前5名供應商發(fā)送特別優(yōu)惠。這個時候在SELECT中就需要同時指定LIMIT子句與ORDER BY子句，查詢優(yōu)化程序?qū)⒉粫雎皂樞?。代碼如下：

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

如果在重現(xiàn)此示例時遇到錯誤，請檢查問題和相關(guān)補丁 Fixed wrong order of "UNION" and "ORDER BY"