Yii Building Search Query Using CDBCommand

I am new in Yii framework, and I found it interesting, so today at work I found a problem to create conditional search query in Yii, of course I can create ordinary SQL text and execute it, but a framework as mature as Yii should have it’s own SQL query helper / generator helper, so I google and mingle with the Yii documentation and found CDBCommand. I avoid Yii ActiveRecord on this case due to performance limitation 🙂

This is a simple example to create query using CDBCommand

$sql = Yii::app()->db->createCommand();
$sql->select('id,name,phone');
$sql->from('{{mytable}}');
$sql->where('id=:ID AND name=:name', array(':ID' => $id, ':name' => $name);

But the thing is, Yii CDBCommand does not work like CodeIgniter’s SQL query helper, you cannot put multiple where to create complex query. Yii behave similar to SQL syntax, you can call multiple join but you can only call select and where once.

So if you an ex-CodeIgniter programmer, you cannot do this in Yii

$sql = Yii::app()->db->createCommand();
$sql->select('id,name,phone');
$sql->from('{{mytable}}');
$sql->where('id=:ID AND name=:name', array(':ID' => $id, ':name' => $name);

if ($phone) {
    $sql->where('phone=:phone', array(':phone' => $phone);
}

The workaround is to use array as where parameter, fill the array as you go and pass it to where once.

So this is an example where I want to filter my data based on optional criterias.

        $sql = Yii::app()->db->createCommand();

        // create the where filter
        $filter_where = array('AND');
        $filter_where_data = array();

        // construct a search/filter query
        if (isset($id)) {
            $filter_where[] = 'people.id=:ID';
            $filter_where_data[':ID'] = $id;
        }

        if (isset($name)) {
            $filter_where[] = 'people.name=:name';
            $filter_where_data[':name'] = $name;
        }

        // a join here.. you can create multiple joins by calling join method multiple times
        $sql->join('{{table_address}} addr', 'addr.addr_id = people.addr_id');

        // construct your query 
        $sql->select('people.id, people.name, addr.description', 'SQL_CALC_FOUND_ROWS');
        $sql->from('{{table_people}} people');
        $sql->limit($pagesize, $offset);

        // where will be generated based on passed filter_where and filter_where_data arrays
        $sql->where($filter_where, $filter_where_data);        

I prefer this method rather than constructing SQL text query by myself, it looks cleaner and in my opinion can construct a cleaner SQL since we use Yii parameter binding and cleanup.

Well, I hope you got the idea 🙂