CodeIgniter Active Record or_where() function produces AND in sql?

Question

I wrote an active record query in CodeIgniter and then I realised that I needed to use OR with to WHERE clauses. So I looked through the docs and found or_where which did what I wanted. But when I use it it produces AND in the output. I couldn't find any other questions on this issue.

I'm using CodeIgniter: 2.1.0

Here is my code (slightly cut down):

$this->db->select("p.*",false);

$this->db->from('projects p');
$this->db->join('customers c', 'p.c_id = c.c_id','left outer');

if(isset($options['add_root']))
  $this->db->or_where('p.p_id=',1,FALSE);

//Get top level projects by default (1) or whatever parent is specified.
if(isset($options['p_id']))
  $this->db->where('p.p_id=',$options['p_id'],false);

$query = $this->db->get();//query

cwallenpoole cwallenpoole · Accepted Answer · 2012-03-14T16:41:15

I don't think you need or_where. I think you need better if/else in PHP.

The logic you probably want:

if(isset($options['p_id']))
{
    // if p_id is set, then look for p_id
    $this->db->where('p.p_id=',$options['p_id'],false);
    // if p_id and add_root are set, then look for p_id OR p_id = 1
    if(isset($options['add_root']))
        $this->db->or_where('p.p_id=',1,FALSE);
}
elseif(isset($options['add_root']))
{
    // look for p_id = 1 only
    $this->db->where('p.p_id=',1,FALSE);
}

Because or_where is first it is simply defaulting to where, and then the subsequent where is the default: an "and".

You could also write the above with a series of elseif's but I view this as less clear:

if(isset($options['p_id']) && isset($options['add_root']))
    $this->db
       ->where('p.p_id=',$options['p_id'],false)
       ->or_where('p.p_id=',1,FALSE);
elseif(isset($options['p_id']) || isset($options['add_root']))
    $this->db
       ->where('p.p_id=',
               // if add_root is set, then 1, otherwise p_id
               (isset($options['add_root'])?1:$options['p_id']),false);

CodeIgniter Active Record or_where() function produces AND in sql?

2 Answers