# Convert mySql to cakephp 3.5

**URL:** <https://discourse.cakephp.org/t/convert-mysql-to-cakephp-3-5/4171>\
**Category:** Need Help\
**Created:** [April 14, 2018, 5:39pm UTC](https://discourse.cakephp.org/t/convert-mysql-to-cakephp-3-5/4171 "2018-04-14T17:39:28Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gari](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@gari](https://discourse.cakephp.org/u/gari)\
**Post date:** [April 14, 2018, 5:39pm UTC](https://discourse.cakephp.org/t/convert-mysql-to-cakephp-3-5/4171/1 "2018-04-14T17:39:28Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [May 1, 2018, 2:40am UTC](https://discourse.cakephp.org/t/convert-mysql-to-cakephp-3-5/4171/2 "2018-05-01T02:40:44Z")

</div>

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](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.

```auto
$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](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.

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [May 5, 2018, 8:49pm UTC](https://discourse.cakephp.org/t/convert-mysql-to-cakephp-3-5/4171/3 "2018-05-05T20:49:53Z")

</div>

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:

```auto
<?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:

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

```

Then in controller method:

```auto
        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.