CodeIgniter Forums
Is method chaining order significant in query builder? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Is method chaining order significant in query builder? (/showthread.php?tid=87022)

Is method chaining order significant in query builder? - dgvirtual - 03-06-2023

When I use this query:

PHP Code:
$mbd = $this->usersModel
   ->select('users.id, first_name, last_name, birthday, active')
   ->join('users_data', 'users.id = users_data.id', 'left')
   ->like('birthday', '-03-')
   ->orLike('birthday', '-04-')
   ->where('active =', 1)
   ->findAll(); 


I get different query string and different results depending on the placement of the WHERE part. The query above ignores the 

PHP Code:
->where('active =', 1) 
part, so I get a result set where the active = 0 as well, but if I push the 

PHP Code:
->where('active =', 1) 
part up before the 

PHP Code:
->like('birthday', '-03-') 
statement, the query executes as expected.


The original query builder command (as written above) produces this query:

Code:
SELECT `users`.`id`, `first_name`, `last_name`, `birthday`, `active` FROM `users` LEFT JOIN `users_data` ON `users`.`id` = `users_data`.`id` WHERE `birthday` LIKE '%-03-%' ESCAPE '!' OR `birthday` LIKE '%-04-%' ESCAPE '!' AND `active` = 1

While the corrected query builder command (with ->where pushed up, getting the right results) generates this:

Code:
SELECT `users`.`id`, `first_name`, `last_name`, `birthday`, `active` FROM `users` LEFT JOIN `users_data` ON `users`.`id` = `users_data`.`id` WHERE `active` = 1 AND `birthday` LIKE '%-03-%' ESCAPE '!' OR `birthday` LIKE '%-04-%' ESCAPE '!'

Ah, and I am using sqlite3, by the way.


Should I consider this a bug, or is there something I misunderstand about the query builder class? Should perhaps the manual be updated regarding the proper ordering of where and like (orLike) clauses?


By the way, if one eliminates the

PHP Code:
->orLike('birthday', '-' . $next_month . '-') 
clause from the original code, the 

PHP Code:
->where('active =', 1) 
is no longer ignored (the code returns results with where condition applied).

RE: Is method chaining order significant in query builder? - iRedds - 03-06-2023

SQL doesn't read minds. The query is executed as written.

a AND b OR c === (a + b) OR c
b OR c AND a === b OR (c + a)

The fact that your query works in one case but fails in another only means that you don't have enough data.

You need to group conditions. a AND (b OR c)

PHP Code:
->groupStart()
   ->like('birthday', '-03-')
   ->orLike('birthday', '-04-')
->groupEnd()
->where('active', 1) 

Or an alternative for SQLite (if the table field is in date format)

PHP Code:
->whereIn('strftime(%m, birthday)', [3, 4], false)
->where('active', 1) 


RE: Is method chaining order significant in query builder? - dgvirtual - 03-06-2023

(03-06-2023, 09:10 AM)iRedds Wrote: SQL doesn't read minds. The query is executed as written.

a AND b OR c  === (a + b) OR c
b OR c AND a  === b OR (c + a)

The fact that your query works in one case but fails in another only means that you don't have enough data.

You need to group conditions. a AND (b OR c)

PHP Code:
->groupStart()
  ->like('birthday', '-03-')
  ->orLike('birthday', '-04-')
->groupEnd()
->where('active', 1) 

Or an alternative for SQLite (if the table field is in date format)

PHP Code:
->whereIn('strftime(%m, birthday)', [3, 4], false)
->where('active', 1) 
Well, thanks! I did not expect I was so inexperienced.