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 🙂

Advertisements

4 comments

  1. Abu · April 8, 2012

    Hi
    Can you write a complete example? I have generate code using gii. Where ill write like
    $sql = Yii::app()->db->createCommand();
    2 $sql->select(‘id,name,phone’);
    3 $sql->from(‘{{mytable}}’);
    4 $sql->where(‘id=:ID AND name=:name’, array(‘:ID’ => $id, ‘:name’ => $name);

    what will be models file, controller file & view file.
    Please send me a complete example. Ill wait your response.

    Thanks
    -Abu

  2. Eru · April 19, 2012

    Start here http://www.yiiframework.com/doc/guide/1.1/en/index 🙂


    Welcome to CodeAreToy. CodeAreToy is not a code writing, tutorial, or consulting service. If you have specific questions, post your code and ask the questions related to that code. If you need someone to write code for you, hire a consultant or contractor. If you need lessons or training, there are schools available for almost every language. (Copied from StackOverflow)

  3. Mariana · September 22, 2014

    I’m also new at Yii. (1.1.x).
    I read your post, but tell me, you could always use the clause andwhere, right?

    So this code
    $sql->where(‘id=:ID AND name=:name’, array(‘:ID’ => $id, ‘:name’ => $name));

    could become
    $sql->where(‘id=:ID’, array(‘:ID’ => $id));
    $sql->andwhere(‘name=:name’, array(‘:name’ => $name));

    • Eru · October 1, 2014

      Isn’t andWhere is on Yii2? for Yii1 I usually use DbCriteria

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s