教程栏目

joomla中文网出品的官方教程
  1. 你目前位置:  
  2. 首页 >
  3. 教程 >
  4. G计划 >
  5. 数据库操作

数据库操作

数据库操作

使用Union方法来查询可以将两个没有实际关联的数据表的查询结果集进行合并。使用UNION组合不同查询的结果有时可能比使用WHERE子句的单个查询快得多，尤其是当查询涉及到其他大表的联接时。

使用基础

为了使用union方法，你需要确认你的SQL服务器是否支持。这些并不是Joomla强制要求的，但是如果你的SQL服务器不支持，那么执行包含union语法的SQL时，就会抛出数据库错误。另外，为了确保union执行成功，每个SELECT查询必须以相同的顺序和兼容的数据类型返回相同数量的字段。

简单的实例

假设你有两张表，一张是客户表(#__zmax_customers),存储了客户的信息，一张是供应商表（#__zmax_suppliers） 存储了供应商的信息。现在你需要同时给所有的客户和供应商发邮件。

获得客户的邮件列表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的查询结果就会将上面两个查询的结果进行合并，并且会移除掉重复的行。如果你不需要移除重复的行，那么可以使用unionAll 来代替union.

多种方法使用Union

在Joomla中，union(unionAll) 方法支持多种方式来使用，可以使用SQL字符串，也可以使用JDatabaseQuery对象或者使用数组。假设我们有3个不同的查询，代码如下：

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

下面的查询都会返回相同的结果：

// 使用JDatabseQuery对象作为参数.
$q1->union($q2)->union($q3);

// 使用JDatabaseQuery对象和字符串作为出纳室
$q1->union($q2)->union('SELECT name, email FROM shareholders');

// 使用数组作为参数
$q1->union(array($q2, $q3));

// union查询和主查询的顺序无关，虽然我们这里使用q2作为主查询，但返回结果和之前是一样的
$q2->union(array($q1, $q3));

union ,unionAll 和unionDistinct

Joomla对SQL中UNION关键词提交了3个方法：

  • union 产生没有重复记录的结果集。在移除重复的结果集的过程中是否会产生性能的下降这个数据库的数据以及数据库的结构有关。
  • unionAll 产生所有的记录的结果集，允许重复
  • unionDistinct 和 union一样。是union的别名。

使用UNION代替OR

在一些情况下，使用union替代 where in  或者 where or 能够显著的提高性能。

假设现在你有一个产品表，现在你想获得两个特定类别中的产品。通过代码如下：

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

此时，你使用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();

当然，不仅仅是两个分类，如果多个分类，你也可以union多个。

给结果集排序

如果需要对结果集进行排序，则需要了解当前的SQL服务器是如果处理ORDER BY的。下面的代码在MYSQL上运行成功，但不保证在其他的数据库上能运行成功。

假设现在你想对输出的结果集按照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语句就不会按照你的希望返回结果：

$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 不能对最终的查询结果进行排序， union查询将会返回一个无序的结果集，上面的查询语句没有语法错误，但是MYSQL的优化器会忽略掉suppliers的 order By.而直接使用customers的order by为最终的结果排序。

解决问题的方法是增加一个用于排序的字段。代码如下：

$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进行排序，且不允许优化器忽略。

假设您想向前10名客户和前5名供应商发送特别优惠。这个时候在SELECT中就需要同时指定LIMIT子句与ORDER BY子句，查询优化程序将不会忽略顺序。代码如下：

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

如果在重现此示例时遇到错误，请检查问题和相关补丁 Fixed wrong order of "UNION" and "ORDER BY"

第 1 页，总共 2 页