Make count query and queryall without doubling the code?

Question

I'm using Yii with Firebird. Let's assume we have following code:

    public function actionGetCustomer($limit = -1, $start = 0) {
            $arr = Yii::app()->db->createCommand()
                            ->select('*')
                            ->from('customer')
                            ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                            ->limit($limit, $start)
                            ->queryAll();
            $ans['arr'] => $arr;
            return $ans;
    }

Then we want to take count of records in db and have following:

    public function actionGetCustomer($limit = -1, $start = 0) {
            $arr = Yii::app()->db->createCommand()
                            ->select('*')
                            ->from('customer')
                            ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                            ->limit($limit, $start)
                            ->queryAll();
            //copy & paste & edit
            $cnt = Yii::app()->db->createCommand()
                            ->select('count(*)')
                            ->from('customer')
                            ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                            ->queryScalar();
            $ans['arr'] => $arr;
            $ans['cnt'] => $cnt;
            return $ans;
    }

And to remove doubling of code (actual query have 10 joins and tons of fields) is it possible to achieve something like this?

    public function actionGetCustomer($limit = -1, $start = 0) {
            $cmd = Yii::app()->db->createCommand()
                            ->from('customer')
                            ->leftJoin('contact', 'customer.idcustomer = contact.idcontact');
            $arr = $cmd
                            ->select('*')
                            ->limit($limit, $start)
                            ->queryAll();
            //copy & paste & edit
            $cnt = $cmd
                            ->select('count(*)')
                            ->queryScalar();
            $ans['arr'] => $arr;
            $ans['cnt'] => $cnt;
            return $ans;
    }

Tried this, but answer from db was cached(?) and despite of changing select part, query scalar was returning result of previous query. Also tried clone and manually copying parts of old command into new but there were errors. As for now the only alternative I see is plain SQL.

Note that first query have limit, whilst second don't.

Answer 1

If you are using MySQL, then you can use the SQL_CALC_FOUND_ROWS fields in your select, and follow this up by by SELECT FOUND_ROWS(). Basically, this works as in the following example :

SELECT SQL_CALC_FOUND_ROWS field1, fields2 FROM mytable
   WHERE fieldx ="somevalue" 
   LIMIT 20;

SELECT FOUND_ROWS();

In yii, you will convert your code to read :

$arr = Yii::app()->db->createCommand()
                 ->select('SQL_CALC_FOUND_ROWS (0), *')
                 ->from('customer')
                 ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                 ->limit($limit, $start)
                 ->queryAll();

$totalRecords   =  Yii::app()->db->createCommand('SELECT FOUND_ROWS()')->queryScalar();

Answer 2

You can just count the array like so:

count($arr);

Which would give you:

public function actionGetCustomer($limit = -1, $start = 0) {
        $arr = Yii::app()->db->createCommand()
                        ->select('*')
                        ->from('customer')
                        ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                        ->limit($limit, $start)
                        ->queryAll();
        return array('arr'=>$arr,'cnt'=>count($arr));
}

As mentioned in the comment bellow this doesnt account for the LIMIT statement. If you don't mind executing a query twice on your DB you can do the following:

$resultCommand = Yii::app()->db->createCommand()
                        ->select('*')
                        ->from('customer')
                        ->leftJoin('contact', 'customer.idcustomer = contact.idcontact')
                        ->limit($limit, $start);
$countCommand = clone($resultCommand); //answer
$countCommand->setSelect('count(*)');
return array(
             'arr'=>$resultCommand->queryAll(),
             'cnt'=>$countCommand->queryScalar()
            );