php 在 Codeigniter 中对 WHERE 子句进行分组

Grouping WHERE clauses in Codeigniter

提问by Nyxynyx

I want to produce the following SQL code using Active Records in Codeigniter:

我想在 Codeigniter 中使用 Active Record 生成以下 SQL 代码：

WHERE name != 'Joe' AND (age < 69 OR id > 50)

Doing the following seems to be as far as I can get, I cant figure out how to group them

做以下似乎尽我所能，我不知道如何将它们分组

$this->db->select()->from('users')->where('name !=', 'Joe')->where('age <', 69)->or_where('id <', $id); 

Any ideas? My SQL query is too complex so I dont wish to rewrite everything in traditional SQL.

有任何想法吗？我的 SQL 查询太复杂了，所以我不想用传统的 SQL 重写所有内容。

UPDATE

更新

My SQL code is dynamically generated depending on the values of certain parameters passed into the model method. The problem with not being able to use parenthesis causes a problem because the operator precedence is such that ANDis evaluated first before OR.

我的 SQL 代码是根据传递给模型方法的某些参数的值动态生成的。无法使用括号的问题会导致问题，因为运算符优先级是AND在 之前首先评估的OR。

*Here is a chunk of my active records code, where there are some other code before and after it:

*这是我的活动记录代码的一部分，在它之前和之后还有一些其他代码：

            ... some $this->db->where() ...
            ... some $this->db->where() ...

    if($price_range) {
        $price_array = explode('.', $price_range);
        for($i = 0; $i < count($price_array); $i++) {
            if($i == 0) {
                $this->db->where('places.price_range', $price_array[$i]);
            } else {
                $this->db->or_where('places.price_range', $price_array[$i]);
            }
        }

    }

            ... some $this->db->where() ...
            ... some $this->db->where() ...

The problem comes because I am using $this->db->or_where()which introduces a ORclause that throws the operator precedence into disarray without being able to use ( )to change the order.

问题来了，因为我正在使用$this->db->or_where()which 引入了一个OR子句，该子句将运算符优先级置于混乱状态而无法( )用于更改顺序。

** Is there any way to solve this? **

** 有没有办法解决这个问题？**

采纳答案by Nyxynyx

Solved. Dynamically generate the SQL query and plug it into $this->db->where(). Thanks guys!

解决了。动态生成 SQL 查询并将其插入$this->db->where(). 谢谢你们！

回答by Aldiyah

In Codeigniter 3.0.3 you can do it simple like this :

在 Codeigniter 3.0.3 中，您可以像这样简单地进行操作：

$this->db->select()
  ->from('users')
  ->where('name !=', 'Joe')
  ->group_start() // Open bracket
  ->where('age <', 69)
  ->or_where('id <', $id)
  ->group_end(); // Close bracket

Perhaps it can help

也许它可以帮助

回答by Jrod

You can use one large string.

您可以使用一根大字符串。

$this->db->select()->from('users')->where("name != 'Joe' AND (age < 69 OR id > 50) ");

$this->db->select()->from('users')->where("name != 'Joe' AND (age < 69 OR id > 50) ");

回答by Thusjanthan Kubendranathan

The grouping of where clauses is not in CI by default. You have to extend the core and add in the ability. I have done so by doing something as follows:

默认情况下，where 子句的分组不在 CI 中。您必须扩展核心并添加能力。我通过以下方式做到了这一点：

class MY_DB_mysql_driver extends CI_DB_mysql_driver 
{       
    public function __construct($params) 
    {
    parent::__construct($params);
    }
    /** 
     * This function will allow you to do complex group where clauses in to c and (a AND b) or ( d and e)
     * This function is needed as else the where clause will append an automatic AND in front of each where Thus if you wanted to do something
     * like a AND ((b AND c) OR (d AND e)) you won't be able to as the where would insert it as a AND (AND (b...)) which is incorrect. 
     * Usage: start_group_where(key,value)->where(key,value)->close_group_where() or complex queries like
     *        open_bracket()->start_group_where(key,value)->where(key,value)->close_group_where()
     *        ->start_group_where(key,value,'','OR')->close_group_where()->close_bracket() would produce AND ((a AND b) OR (d))
     * @param $key mixed the table columns prefix.columnname
     * @param $value mixed the value of the key
     * @param $escape string any escape as per CI
     * @param $type the TYPE of query. By default it is set to 'AND' 
     * @return db object.  
     */
    function start_group_where($key,$value=NULL,$escape,$type="AND")
    {
        $this->open_bracket($type); 
        return parent::_where($key, $value,'',$escape); 
    }

    /**
     * Strictly used to have a consistent close function as the start_group_where. This essentially callse the close_bracket() function. 
     */
    function close_group_where()
    {
        return $this->close_bracket();  
    }

    /**
     * Allows to place a simple ( in a query and prepend it with the $type if needed. 
     * @param $type string add a ( to a query and prepend it with type. Default is $type. 
     * @param $return db object. 
     */
    function open_bracket($type="AND")
    {
        $this->ar_where[] = $type . " (";
        return $this;  
    }   

    /**
     * Allows to place a simple ) to a query. 
     */
    function close_bracket()
    {
        $this->ar_where[] = ")"; 
        return $this;       
    }
}

Usage:

用法：

group_where_start(key,value)->where(key,value)->group_where_close() 

or

或者

complex queries like

复杂的查询，如

open_bracket()->start_group_where(key,value)->where(key,value)->close_group_where()->start_group_where(key,value,'','OR')->close_group_where()->close_bracket() would produce AND ((a AND b) OR (d))

回答by timw4mail

What I've done is duplicate the and clause after the where, which is effectively the same as the long string selection.

我所做的是在 where 之后复制 and 子句，这实际上与长字符串选择相同。

$this->db->select()
  ->from('users')
  ->where('name !=', 'Joe')
  ->where('age <', 69)
  ->or_where('id <', $id)
  ->where('name !=', 'Joe');

The one large string way is probably better.

一个大的字符串方式可能更好。

回答by Sachem

CI3 has all you need!

CI3有你需要的一切！

$this->db->select('*')->from('my_table')
        ->group_start()
                ->where('a', 'a')
                ->or_group_start()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->group_end()
        ->group_end()
        ->where('d', 'd')
->get();

https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping