In CakePHP 3, is it possible to run a custom SQL query from within a table model? If so, how?
I tried the following from within a table model:
public function getUsersByLocation($location) { $sql = "SELECT `user_id`, `username` FROM `user` WHERE `location` = ?"; $rows = $this->query($sql, [$location]); }
(The query is deliberately simple for example purposes.)
But then, when I loop through the $rows
, this results in an infinite loop.
I’m surprised CakePHP 3 does not clearly document running custom queries from a table model anywhere in their documentation. They are currently forcing users to use CakePHP’s own numerous proprietary methods to glue together various queries.
Advertisement
Answer
Here is the answer:
public function getUsersByLocation($location) { $sql = "SELECT `user_id`, `username` FROM `user` WHERE `location` = ?"; return $this->connection()->execute($sql, [$location])->fetchAll('assoc'); }