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.

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