CakePHP3: How to run custom SQL query in table model

Question

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.

There is simply no point in your case to use a custom query. It is in any case inferior to using the ORM directly. But it is very well documented here to do custom ones. — mark
– mark, Commented Feb 8, 2016 at 23:07
So from within a table model, I need to get the database connection (since it presumably was not passed in via dependency injection), and run the query on that? What an atrocious framework. — Leo Galleguillos
– Leo Galleguillos, Commented Feb 9, 2016 at 3:03
No, from withtin a table, you don't need to get the connection from the outside world, the connection is by default injected via the constructor, and can be accessed via the Table::connection() method (for tables this will by default return an instance of \Cake\Database\Connection). Maybe you should pay a little more attention to the docs of the oh so horrible framework that you are using. — ndm
– ndm, Commented Feb 9, 2016 at 11:28
And don't blame the framework if you are abusing things in a really bad way. In general: If you need to workaround sth you usually don't use the recommended best practice approach. The less lines, the better. — mark
– mark, Commented Feb 9, 2016 at 16:57
@mark Where in my example is anything being abused in a really bad way? If you have an elegant answer, please post it, and I will accept it. I would be glad to change my opinion on this framework. — Leo Galleguillos
– Leo Galleguillos, Commented Feb 10, 2016 at 0:42

Leo Galleguillos · Accepted Answer · 2021-04-27 23:25:32Z

3

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

edited Apr 27, 2021 at 23:25

answered Feb 11, 2016 at 1:37

Leo Galleguillos

2,8203 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jose Diaz-Gonzalez Over a year ago

You can also just pull in the eloquent orm and use that directly instead of using the CakePHP orm. Since both are composer-compatible projects, this should be pretty straightforward.

Jose Diaz-Gonzalez Over a year ago

Great to hear that we think alike. As a CakePHP core developer, I hate to see our users complain about software when simple solutions are available to them. Best of luck :)

Aman Kumar · Accepted Answer · 2020-09-02 05:46:14Z

1

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.

edited Sep 2, 2020 at 5:46

Aman Kumar

4,5573 gold badges21 silver badges40 bronze badges

answered Feb 10, 2016 at 16:43

mark

21.8k3 gold badges54 silver badges75 bronze badges

Collectives™ on Stack Overflow

CakePHP3: How to run custom SQL query in table model

2 Answers 2

2 Comments

So simply use php

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

So simply use php

Comments

Your Answer

Sign up or log in

Post as a guest

Related