Convert mySql to cakephp 3.5


Dear Gurus,

I need help to convert Mysql query into cakephp 3.5. The query is for calculate player ranking base on column “points”. I test the query and work fine but I dont have the expertise to convert to ORM query builder cake.

FROM view_ent_users)) as rank
FROM view_ent_users
WHERE nickname = ‘Sebass’

This is the query:



Active record (cakes orm) is a shortcut language (not a programming language) and not sql, but at runtime gets converted right back to the normal sql in order to be executed.

If you work the tutorials and examples here you will learn active record shortcuts.

Look at them like linq-to-sql in asp,net was a shortcut language. But even in laravel when a query starts getting too complex, or group by is needed, or a union, or a large amount of data, the orm isn’t so great.

But that’s up to you. You also have the option of getting the pdo instance and write a regular query, i.e.

$connection = ConnectionManager::get('default');
$result = $connection->execute('SELECT COUNT(dogid) as total FROM dc_dogs WHERE adopted = 0')->fetchAll('assoc');

The pdo instance also allows for proper data binding using $stmt->bindValue, as example.

I have not done a guide here, but I did a guide for laravel. See

Same can be done in cakephp with the pdo instance. If you have a look you will see the binding of parameters.

I will try to do one for cake when I get the time.


A quick tutor. Say you want to run some normal PDO in cake, but not bloat your controller. Here is quick example

Just a test model for example:


namespace App\Model\SMVC;

use Cake\Datasource\ConnectionManager as CM;  // CM is shorter

class TestModel {

    public static function getDogs($offset = "", $rowsperpage = "", $adopted = 0) {
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        $dbh = CM::get('default');
        $sql = "SELECT * FROM dc_dogs WHERE adopted = :adopted ORDER BY sex, lastedit DESC " . $pagingQuery;
        $stmt = $dbh->prepare($sql);
        $stmt->bindValue(":adopted", $adopted);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);


In the calling controller have at top:

use App\Model\SMVC\TestModel as TM; // Again TM shorter, easier

Then in controller method:

        public function index() {
        // request as required
        $dogrows = DBS::dogCount($dogsearch);  // paging needs count
        $pages = new HelpersPaginator('3', 'p');    //custom pagination
        $pageLinks = $pages->pageLinks();
        $adopted = 0;  // Just passing test variable
        $query = TM::getDogs($pages->getLimit2(), $pages->getPerpage(), $adopted);
        $this->set('title', 'Dogs');
        $this->set(compact('query', 'pageLinks'));

Again just sample usage of native PDO instance, and a way to handle a possible custom query if the ORM or Query builder doesn’t work out for the query.