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

I need to create query like (i'm using PostgreSQL):

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

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

So, I'm trying to use query builder

所以，我正在尝试使用查询构建器

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

But in result i have sql query where selects without brackets. Like this:

但结果我有sql查询，其中选择没有括号。喜欢这个：

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

And get SQL syntax error... Sorry for my english

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

2 个解决方案

#1

Why not SELECT * FROM t1 WHERE a != b ORDER BY a DESC?

为什么不选择SELECT * FROM t1 WHERE a！= b ORDER by DESC？

EDIT
For more complex query, when you really need two or more ORDER BY, I can see only this solution:

编辑对于更复杂的查询，当你真的需要两个或更多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();

#2

Another way to solve this problem is modify class Database_Query_Builder_Select. This way create unions query with brackets like: (select_1) union (select_2) union (select_n). And you can use ORDER BY and LIMIT in each SELECT statement

解决此问题的另一种方法是修改类Database_Query_Builder_Select。这种方式使用括号创建联合查询：（select_1）union（select_2）union（select_n）。并且您可以在每个SELECT语句中使用ORDER BY和LIMIT

1) Create folder application/classes/database/query/builder/
2) Create file select.php
3) Insert into created file this code:

1）创建文件夹应用程序/ 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;
}

}

}