Some times in SQL is very usefull and efficiency to use query from query using IN function. Some project require it. But Cakephp does not generate such queries and we need to create such requests to DB by hand.

Look at example. We have users in groups. So we want choose a users has same groups with specific user $u1.

select * from users
where group_id in (select group_id from users where id=$u1).

How to make such query in CakePHP.

It is possible to make it only in most handly way.
$this->User->findAll(array(’group_id’ => ”in (select group_id from users where id=$u1)”));

But when we work with Cake much more pretty to make all queries in similar sintax sugar type.

What about to rewrite previours example in next way:

$this->User->findAll(array(’group_id’ => ”in -!(”. $this->User->getQuery(array(’id’=> $u1) . ’)', ’group_id’)));

Function Model::getQuery shuld take similar parameter as Model::findAll method and return SQL query to DB.

Is this possible to create such function without changing CORE files?
My answer is YES!!!
Really all what we need is extending of Model and DboMysql classes.
We need to add two methods - both are based on cake core libraries (Model::findAll and DboSource::read).

For Model class all pretty simple. We just place our new function to AppModel or to some behavior.
But what about extending DboMysql driver class? Is this possible. Really cakephp 1.2 support such feature.
We need to create class extension in folder app/model/datasource/dbo
I name it DboMysqlEx – file is dbo_mysql_ex.php.

Configuration:

We need to use other db connection for model User all for all model where we need new feature.

In app/config/database.php we need to use new driver: ‘driver’ => ‘mysql_ex’,

DATABASE_CONFIG


class DATABASE_CONFIG {     var $default = array(
        ’driver’ => ’mysql_ex’,
        ’persistent’ => false,
        ’host’ => ’localhost’,
        ’login’ => ’root’,
        ’password’ => ”,
        ’database’ => ’testing’,
        ’prefix’ => ”
    );
}

  

APP_MODEL:

Model Class:

<?php 
class AppModel extends Model{
    function getQuery($conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null) {
        $db =& ConnectionManager::getDataSource($this->useDbConfig);
        $this->id = $this->getID();
        $offset = null;

        if (empty($page) || !is_numeric($page) || intval($page) < 1) {
            $page = 1;
        }

        if ($page > 1 && $limit != null) {
            $offset = ($page - 1) * $limit;
        }

        if ($order == null && $order !== false) {
            if ($this->order == null) {
                $order = array();
            } else {
                $order = array($this->order);
            }
        } else {
            $order = array($order);
        }

        $queryData = array(
            ‘conditions’ => $conditions,
            ‘fields’    => $fields,
            ‘joins’     => array(),
            ‘limit’     => $limit,
            ‘offset’    => $offset,
            ‘order’     => $order
        );

        if (!empty($this->behaviors)) {
            $behaviors = array_keys($this->behaviors);
            $ct = count($behaviors);
            for ($i = 0; $i < $ct; $i++) {
                $ret = $this->behaviors[$behaviors[$i]]->beforeFind($this, $queryData);
                if (is_array($ret)) {
                    $queryData = $ret;
                } elseif ($ret === false) {
                    return null;
                }
            }
        }

        $ret = $this->beforeFind($queryData);
        if (is_array($ret)) {
            $queryData = $ret;
        } elseif ($ret === false) {
            return null;
        }

        return $db->queryGet($this, $queryData, $recursive);
    }
}
?>

app/model/datasource/dbo/dbo_mysql_ex.php

Model Class:

<?php 
 
require (LIBS . ‘model’ . DS . ‘datasources’ . DS . ‘dbo’ . DS . ‘dbo_mysql.php’);
 
class DboMysqlEx extends DboMysql {
/**
 * Enter description here…
 *
 * @var unknown_type
 */
    var $description = “MySQL DBO Extension Driver”;
    
    function queryGet(&$model, $queryData = array(), $recursive = null) {
        $this->__scrubQueryData($queryData);
        $null = null;
        $array = array();
        $linkedModels = array();
        $this->__bypass = false;

        if (!is_null($recursive)) {
            $_recursive = $model->recursive;
            $model->recursive = $recursive;
        }

        if (!empty($queryData['fields'])) {
            $this->__bypass = true;
            $queryData['fields'] = $this->fields($model, null, $queryData['fields']);
        } else {
            $queryData['fields'] = $this->fields($model);
        }

        foreach ($model->__associations as $type) {
            foreach ($model->{$type} as $assoc => $assocData) {
                if ($model->recursive > -1) {
                    $linkModel =& $model->{$assoc};

                    $external = isset($assocData['external']);
                    if ($model->name == $linkModel->name && $type != ‘hasAndBelongsToMany’ && $type != ‘hasMany’) {
                        if (true === $this->generateSelfAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
                            $linkedModels[] = $type . ‘/’ . $assoc;
                        }
                    } else {
                        if ($model->useDbConfig == $linkModel->useDbConfig) {
                            if (true === $this->generateAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
                                $linkedModels[] = $type . ‘/’ . $assoc;
                            }
                        }
                    }
                }
            }
        }
        // Build final query SQL
        return $this->generateAssociationQuery($model, $null, null, null, null, $queryData, false, $null);
    }
    
    
}
?>