Nested where clauses codeigniter mysql query

问题:

Is there any way to get nested where clauses? e.g.:

SELECT * FROM table WHERE (colA = 'valueA' AND colB = 'valueB') OR (colA = 'valueC' AND colB = 'valueD')

I know I could just write this into a query function call e.g.:

$this->db->query("SELECT ...")

But I was wondering if there was a "proper" way to do it in codeigniter e.g.:

$this->db->where(array('colA'=>'valueA'), array('colB'=>valueB'))->or_where(array('colA'=>'valueC'), array('colB'=>'valueD'))

thanks

回答1:

With codeigniter 3, now there is, see the update!
There's no where() method usage variant with arrays that would allow you to do that. In these situations i usually just build the part in one long string like this:

$this->db->where("
    (
    (colA = '".$this->db->escape($v0)."' and colB = '".$this->db->escape($v1)."') 
    or 
    (colA = '".$this->db->escape($v2)."' and colB = '".$this->db->escape($v3)."') 
    )
");

Escaping can be done with escape(does some autodetection) or escape_str or escape_like_str manually depending on what the parameter expected to be or what the predicate in use.

If i'm on a project that uses the Datamapper library, i prefer to use the group_start() and group_end() methods when building these kind of queries, they have a lot of different flavor of these.

Update

Now with, Codeigniter 3 which have grouping methods in the query builder, so you can do ->group_start()s and ->group_end()s.

回答2:

You can also try like

$this->db->where(condition1);
$this->db->or_where(condition2);