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.

Check this link may be this might help you http://www.yiiframework.com/doc/api/1.1/CPagination — Neeraj Kumar, Mar 13 '15 at 09:47
Thanks, but I use Yii in couple with ExtJS and it's paginating. And should use queries like that and not models. — Flame, Mar 13 '15 at 10:28

score 1 · Accepted Answer · edited Mar 13 '15 at 11:35

1

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()
            );

edited Mar 13 '15 at 11:35

Flame

71
1
8

answered Mar 13 '15 at 09:19

Jelle de Fries

885
1
11
20

Thanks for answer, but I forgot to make obvious one thing: I want to get count without limit. First query basically is a page, and second query is count of records. – Flame Mar 13 '15 at 09:42
If you don't mind executing the query twice, you can just copy the first command and change the select statement. – Jelle de Fries Mar 13 '15 at 09:53
Nope, that won't do it. In PHP assignment is made by reference. So that command will be executed, have some cache and next - "other" command will use that cache. And yet again limit is not excluded from count in your example. – Flame Mar 13 '15 at 10:23
1: http://php.net/manual/en/language.oop5.references.php 2: count doesnt care for limit in mysql: http://stackoverflow.com/questions/17020842/mysql-count-with-limit (Assuming you use mySQL) – Jelle de Fries Mar 13 '15 at 10:28
You should read my code carefully, I don't execute the command. I only create it and copy it to count command. I execute it after. But I have not tried this code, so maybe you are right and it doest work :) – Jelle de Fries Mar 13 '15 at 10:32
I tried :) And that isn't working. Using Firebird, by the way. And now will check if count there ignores limit. – Flame Mar 13 '15 at 10:40
You are right with the reference thing (im used to php 4) maybe you can do it like this?: http://stackoverflow.com/questions/185934/how-do-i-create-a-copy-of-an-object-in-php – Jelle de Fries Mar 13 '15 at 10:43
Uhh, many thanks. That was question about cloning... So, I accept your answer. You may change $countCommand = $resultCommand; to use clone($resultCommand) and that will do it. Gosh, such a simple answer... – Flame Mar 13 '15 at 10:53

score 0 · Answer 2 · answered Mar 24 '15 at 06:48

0

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();

answered Mar 24 '15 at 06:48

crafter

6,246
1
34
46

Well, if it works on MySQL, then it's great. I'm using Firebird, though. Edited question to clarify this. – Flame Mar 24 '15 at 15:04
http://stackoverflow.com/questions/4231313/whats-the-same-function-of-found-rows-in-firebird – crafter Mar 24 '15 at 15:10
So, simply saying, it's up to two queries as Jelle de Fries suggested. – Flame Mar 24 '15 at 15:18

Make count query and queryall without doubling the code?

2 Answers2