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.

Answers


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

Search




Leave an answer