Kohana：如何将查询括在括号中？

Question

我需要创建像这样的查询（我正在使用 PostgreSQL）：

(SELECT * FROM t1 WHERE a>b ORDER BY a DESC)  
UNION ALL  
(SELECT * FROM t1 WHERE a<b ORDER BY a DESC)  

所以，我正在尝试使用查询生成器

$query1 = DB::select('*')  
->from('t1')->where('a', '>', 'b')->order_by('a', 'desc');    

$query2 = DB::select('*')  
->from('t1')->where('a', '<', 'b')->order_by('a', 'desc');    

$result = $query1->union($query2, TRUE);  

，但结果我有 sql 查询，其中选择不带括号。像这样：

SELECT * FROM t1 WHERE a>b ORDER BY a DESC  
UNION ALL  
SELECT * FROM t1 WHERE a<b ORDER BY a DESC  

并得到 SQL 语法错误...对不起我的英语

Answer 1

为什么不SELECT * FROM t1 WHERE a != b ORDER BY a DESC？

编辑
对于更复杂的查询，当你确实需要两个或多个ORDER BY时，我只能看到这个解决方案：

$query1 = DB::select()
        ->from('t1')
        ->where('a', '>', 'b')
        ->order_by('a', 'DESC')
        ->__toString();


$query2 = DB::select()
        ->from('t1')
        ->where('a', '<', 'b')
        ->order_by('a', 'DESC')
        ->__toString();

$query3 = '(' . $query1 . ') UNION ALL (' . $query2 . ')';
$result = DB::query(Database::SELECT, $query3)->execute();

Answer 2

解决此问题的另一种方法是修改class Database_Query_Builder_Select。这样可以创建带括号的联合查询，例如：(select_1) union (select_2) union (select_n)。您可以在每个 SELECT 语句中使用 ORDER BY 和 LIMIT

1) 创建文件夹 application/classes/database/query/builder/
2）创建文件select.php
3）将此代码插入到创建的文件中：

<?php defined('SYSPATH') or die('No direct script access.');

class Database_Query_Builder_Select extends Kohana_Database_Query_Builder_Select {

protected $_union_limit = NULL;

/**
 * Insert LIMIT statement after all unions
 * 
 * @param integer $number
 */
public function union_limit($number)
{
    $this->_union_limit = (int) $number;

    return $this;
}

/**
 * Compile the SQL query and return it.
 *
 * @param   object  Database instance
 * @return  string
 */
public function compile(Database $db)
{
    // Callback to quote columns
    $quote_column = array($db, 'quote_column');

    // Callback to quote tables
    $quote_table = array($db, 'quote_table');

    // Start a selection query
    $query = 'SELECT ';
    if ( ! empty($this->_union)) $query = DB::expr('(SELECT ');

    if ($this->_distinct === TRUE)
    {
        // Select only unique results
        $query .= 'DISTINCT ';
    }

    if (empty($this->_select))
    {
        // Select all columns
        $query .= '*';
    }
    else
    {
        // Select all columns
        $query .= implode(', ', array_unique(array_map($quote_column, $this->_select)));
    }

    if ( ! empty($this->_from))
    {
        // Set tables to select from
        $query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from)));
    }

    if ( ! empty($this->_join))
    {
        // Add tables to join
        $query .= ' '.$this->_compile_join($db, $this->_join);
    }

    if ( ! empty($this->_where))
    {
        // Add selection conditions
        $query .= ' WHERE '.$this->_compile_conditions($db, $this->_where);
    }

    if ( ! empty($this->_group_by))
    {
        // Add sorting
        $query .= ' GROUP BY '.implode(', ', array_map($quote_column, $this->_group_by));
    }

    if ( ! empty($this->_having))
    {
        // Add filtering conditions
        $query .= ' HAVING '.$this->_compile_conditions($db, $this->_having);
    }

    if ( ! empty($this->_order_by))
    {
        // Add sorting
        $query .= ' '.$this->_compile_order_by($db, $this->_order_by);
    }

    if ($this->_limit !== NULL)
    {
        // Add limiting
        $query .= ' LIMIT '.$this->_limit;
    }

    if ($this->_offset !== NULL)
    {
        // Add offsets
        $query .= ' OFFSET '.$this->_offset;
    }

    if ( ! empty($this->_union))
    {
        $iteration = 1;
        foreach ($this->_union as $u) {
            if ($iteration == 1) $query .= ')';
            $query .= ' UNION ';
            if ($u['all'] === TRUE)
            {
                $query .= 'ALL ';
            }
            $query .= '('.$u['select']->compile($db).')';

            $iteration++;
        }
    }

    if ( ! empty($this->_union_limit))
    {
        $query .= ' LIMIT '.$this->_union_limit;
    }
    $this->_sql = $query;

    return $query;
}

}