Grouping WHERE clauses in Codeigniter

Question

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

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.

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 AND is evaluated first before 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 OR clause that throws the operator precedence into disarray without being able to use ( ) to change the order.

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

it doesnt' seem to be too complex to don't let your write it in plain SQL — dynamic, Jun 24 '11 at 15:50
@yes123: the example above is just an example, my active records alone generate up to 60+ lines of dynamically generated actual SQL code of which many are conditional SQL statements. — Nyxynyx, Jun 24 '11 at 17:38
This is an unfortunate limitation of the Activerecord class in Codeigniter. — timw4mail, Jun 24 '11 at 18:00
@timw4mail: any suggestion what I should do? Any alternatives to rewriting all in plain SQL? — Nyxynyx, Jun 24 '11 at 18:02
@Nyxynyx the best way is probably to do the where clause as a plain sql string. The $this->db->where() method can take a plain string. — timw4mail, Jun 24 '11 at 18:05
@timew4mail: looks like I have to construct the plain SQL code with parenthesis using PHP, then drop it into `$this->db->where()`, guess that may work — Nyxynyx, Jun 24 '11 at 18:13

score 32 · Answer 1 · edited Feb 28 '23 at 19:19

In Codeigniter 3 you can use:

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

In Codeigniter 4 you can use:

$builder->select('*')
  ->from('users')
  ->where('name !=', 'Joe')
  ->groupStart()
    ->where('age <', 69)
    ->orWhere('id <', $id)
  ->groupEnd();

Perhaps it can help

score 15 · Answer 2 · answered Jun 24 '11 at 15:52

15

You can use one large string.

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

answered Jun 24 '11 at 15:52

Jared

12,406
1
35
39

I have updated the original question with some actual code from my project... As you can see I cannot use parenthesis `( )` like you do in your answer – Nyxynyx Jun 24 '11 at 17:55
@Nyxynyx So in the end your solution was to create one large string? – Jared Jun 24 '11 at 19:14
Yup, I created a partial plain SQL query string for the problematic part that requires parenthesis) and inserted it into `$this-db->where()` – Nyxynyx Jun 26 '11 at 16:47

score 13 · Answer 3 · edited May 30 '13 at 08:09

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:

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

Excelent !!! I customize my driver. Did you suggest it to CI ? Can I put this into activeRec ? — Nicolas Thery, Mar 19 '14 at 11:48
Great! It really helped me out! One might want to read this to get it working: https://github.com/bcit-ci/CodeIgniter/wiki/Extending-Database-Drivers — f4der, Nov 05 '14 at 15:42
Hey all, sorry been a while. No I haven't suggested it to CI. Just came out of necessity for me. Feel free to put it into activeRec @NicolasThery. Please set this as the correct answer so I can start posting more solutions. Cheers :) — Thusjanthan Kubendranathan, Dec 01 '16 at 07:21

score 9 · Answer 4 · answered May 23 '17 at 14:21

CI3 has all you need!

$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

score 7 · Answer 5 · answered Jun 24 '11 at 17:07

7

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

$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.

answered Jun 24 '11 at 17:07

timw4mail

1,716
2
13
16

After trying in vain to get the parentheses right as described in @Jrod's solution, I took this approach which worked for me! Thanks. – user1072910 Sep 18 '14 at 20:06

score -9 · Accepted Answer · answered Jun 24 '11 at 18:25

-9

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

answered Jun 24 '11 at 18:25

Nyxynyx

61,411
155
482
830

baaad, you loose string validation – Nicolas Thery Mar 19 '14 at 11:48

Grouping WHERE clauses in Codeigniter

6 Answers6

Linked

Related