CODEWITHSUNDEEP
phpcodeigniteractiverecord
Adamantus
Adamantus

Reputation: 821

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

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

Upvotes: 0

Views: 3259

Answers (2)

Rocky
Rocky

Reputation: 304

There's a small error in the order of the query that you're trying to run. You can add multiple 'where' clause which will get converted to a query with an 'AND' in between. But if you wanna use 'OR' instead you use a 'or_where'.

In your query you've used an 'or_where' clause, which is correct but you've used 'where' after that, which literally adds up to the previous query. So, you gotta use the 'where' clause first and then use the 'or_where' clause.

Just change the order and it would work.

Upvotes: 1

cwallenpoole
cwallenpoole

Reputation: 82028

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

Upvotes: 1

Related Questions