blocks|key|261239|text|您可以使用一个大字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|261240|$this->db->select()->from('users')->where("name+!=+'Joe'+AND+(age+<+69+OR+id+>+50)+");|offset|length|style|CODE|261241|entityMap^0|0|0|2E|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

You can use one large string.

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

blocks|key|266792|text|我所做的是在where后面复制and子句，这实际上与长字符串选择相同。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|266793|$this->db->select()
++->from('users')
++->where('name+!=',+'Joe')
++->where('age+<',+69)
++->or_where('id+<',+$id)
++->where('name+!=',+'Joe');|code-block|syntax|javascript|266794|一个大的字符串方式可能更好。|266795|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>$this-&gt;db-&gt;select()
 -&gt;from('users')
 -&gt;where('name !=', 'Joe')
 -&gt;where('age &lt;', 69)
 -&gt;or_where('id &lt;', $id)
 -&gt;where('name !=', 'Joe');
</code></pre>

The one large string way is probably better.

blocks|key|2956369|text|解决了。动态生成SQL查询并将其插入到$this->db->where()中。谢谢你们！|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2956370|entityMap^0|J|I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Solved. Dynamically generate the SQL query and plug it into <code>$this-&gt;db-&gt;where()</code>. Thanks guys!

blocks|key|3170506|text|默认情况下，where子句的分组不在CI中。您必须扩展核心并添加功能。我这样做的方法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3170507|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;+++++++
++++}
}|code-block|syntax|javascript|3170508|用法：|3170509|group_where_start(key,value)->where(key,value)->group_where_close()+|3170510|或|3170511|复杂查询，如|3170512|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))|3170513|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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: 

<pre><code>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)-&gt;where(key,value)-&gt;close_group_where() or complex queries like
 * open_bracket()-&gt;start_group_where(key,value)-&gt;where(key,value)-&gt;close_group_where()
 * -&gt;start_group_where(key,value,'','OR')-&gt;close_group_where()-&gt;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-&gt;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-&gt;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-&gt;ar_where[] = $type . " (";
 return $this; 
 } 

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

Usage: 

<pre><code>group_where_start(key,value)-&gt;where(key,value)-&gt;group_where_close() 
</code></pre>

or 

complex queries like

<pre><code>open_bracket()-&gt;start_group_where(key,value)-&gt;where(key,value)-&gt;close_group_where()-&gt;start_group_where(key,value,'','OR')-&gt;close_group_where()-&gt;close_bracket() would produce AND ((a AND b) OR (d))
</code></pre>

blocks|key|2956496|text|在Codeigniter+3.0.3中，你可以像这样简单地完成：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2956497|$this->db->select()
++->from('users')
++->where('name+!=',+'Joe')
++->group_start()+//+Open+bracket
++->where('age+<',+69)
++->or_where('id+<',+$id)
++->group_end();+//+Close+bracket|code-block|syntax|javascript|2956498|也许它能帮上忙|2956499|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In Codeigniter 3.0.3 you can do it simple like this :

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

Perhaps it can help

blocks|key|3170545|text|CI3提供了您所需的一切！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3170546|$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();|code-block|syntax|javascript|3170547|https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping|offset|length|3170548|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|29|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

CI3 has all you need!

<pre><code>$this-&gt;db-&gt;select('*')-&gt;from('my_table')
 -&gt;group_start()
 -&gt;where('a', 'a')
 -&gt;or_group_start()
 -&gt;where('b', 'b')
 -&gt;where('c', 'c')
 -&gt;group_end()
 -&gt;group_end()
 -&gt;where('d', 'd')
-&gt;get();
</code></pre>

<a href="https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping" rel="noreferrer">https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping</a>

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

<pre><code>WHERE name != 'Joe' AND (age &lt; 69 OR id &gt; 50)
</code></pre>

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

<pre><code>$this-&gt;db-&gt;select()-&gt;from('users')-&gt;where('name !=', 'Joe')-&gt;where('age &lt;', 69)-&gt;or_where('id &lt;', $id); 
</code></pre>

Any ideas? My SQL query is too complex so I dont wish to rewrite everything in traditional 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 <code>AND</code> is evaluated first before <code>OR</code>. 

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

<pre><code> ... some $this-&gt;db-&gt;where() ...
 ... some $this-&gt;db-&gt;where() ...

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

 }

 ... some $this-&gt;db-&gt;where() ...
 ... some $this-&gt;db-&gt;where() ...
</code></pre>

The problem comes because I am using <code>$this-&gt;db-&gt;or_where()</code> which introduces a <code>OR</code> clause that throws the operator precedence into disarray without being able to use <code>( )</code> to change the order.

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

Grouping WHERE clauses in Codeigniter

我想在Codeigniter中使用Active Records生成以下SQL代码：WHERE name != 'Joe' AND (age < 69 OR id > 50)执行以下操作似乎是我所能做的，我想不出如何对它们进行分组$this->db->select()->from('users')->where('nam...

问Codeigniter中的WHERE子句分组
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Codeigniter中的WHERE子句分组EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Codeigniter中的WHERE子句分组
EN