Home Ask Login Register

Developers Planet

Your answer is one click away!

hello world February 2016

CakePHP3: How to run custom SQL query in table model

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.


mark February 2016

So simply use

php $query = $this->find() ->select(['user_id', 'username']) ->where('location' => $location); within your Table class.

This is all documented very clearly in the docs.

hello world February 2016

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

Holy chained methods batman. We gotta use a better framework asap. Notice that none of the companies using CakePHP are tech companies. Have a nice day.

Post Status

Asked in February 2016
Viewed 3,345 times
Voted 6
Answered 2 times


Leave an answer

Quote of the day: live life