Convert mySql to cakephp 3.5

Need Help
1

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.

SELECT *, FIND_IN_SET(points,
(SELECT GROUP_CONCAT(DISTINCT points ORDER BY points DESC)
FROM view_ent_users)) as rank
FROM view_ent_users
WHERE nickname = ‘Sebass’
ORDER BY points DESC

This is the query:

Thanks,

2

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 https://book.cakephp.org/3.0/en/orm.html 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

https://laracasts.com/discuss/channels/guides/getpdo-usage

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.

3

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:

<?php

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);
        $stmt->execute();
        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
        $pages->setTotal($dogrows);
        $pageLinks = $pages->pageLinks();
        $adopted = 0;  // Just passing test variable
        $query = TM::getDogs($pages->getLimit2(), $pages->getPerpage(), $adopted);
        $this->viewBuilder()->layoutPath('/Dog');
        $this->viewBuilder()->layout('indextp');
        $this->set('title', 'Dogs');
        $this->set(compact('query', 'pageLinks'));
        $this->render('/Dogs/index');
    }

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.