Codeigniter Active Record query order

Question

What's the difference between this query:

$this->db->select('*');
$this->db->from('logs AS l');
$this->db->join('log_group_ref AS r', 'l.log_id = r.log_id');
$this->db->like('l.log_title', 'hello');
$this->db->or_like('l.log_content', 'hello');
$this->db->where('r.group_id', 1);

and this:

SELECT * FROM logs as l
join log_group_ref as r
on l.log_id = r.log_id
where l.log_title like "%hello%"
or l.log_content like "%hello%"
and r.group_id = 1

When I run the CI Active record way, I get different result against the generic query.

When I intentionally put error on the CI query, like removing a letter from a field to see the error on the console, it moves the WHERE clause after the JOIN clause.

And I think it makes a difference because I receive different results from both.

Any idea how to fix this?

Or I'm running a wrong query?

Update:

As requested, here's the CI generated query:

Error Number: 1054

Unknown column 'r.group_i' in 'where clause'

SELECT `*` FROM (`logs` AS l)JOIN `log_group_ref` AS r ON 
`l`.`log_id` = `r`.`log_id`JOIN `users` AS u ON `u`.`user_id`=`l`.`log_author`WHERE 
`r`.`group_i` = '1' AND `l`.`log_title` LIKE '%at%'OR `l`.`log_content` LIKE '%at%'

Filename: C:\xampp\htdocs\done\system\database\DB_driver.php

Line Number: 330

Here, I intentionally remove the 'd' in r.group_id in the last WHERE clause.

Paste the CI generated query in the question to see the difference — Shaolin
$this->output->enable_profiler(TRUE); and you will see all the queryes that you run — ddjikic

ddjikic ddjikic · Accepted Answer · 2013-03-07T09:06:34

You have a diference because its a difererece if you selecting

( someting like '%a'% or someting like '%b' ) and someting = 1
someting like '%a'% or ( someting like '%b'  and someting = 1)
someting like '%a'% or  someting like '%b'  and someting = 1

so there is the diference as i wrote in the comment use

$this->output->enable_profiler(TRUE);

to se what CI did you will se queries and the end of the page and whole lot of oder data but to use ( ) inside CI you need someting like

$this->db->where('(someting like "%'.$a.'%" or someting like "%'.$b.'%" )',null,false)

i hope it helps

Codeigniter Active Record query order

1 Answers