CODEWITHSUNDEEP
yii
Andrew
Andrew

Reputation: 987

Yii model findAll() and count() return different number of results

UPDATED: see the end of the question

I'm working with Yii (and RESTFullYii in particular but I doubt that is relevant to the question)

There is a CDbCriteria for a model:

        $criteria = new CDbCriteria(
                array(
                        'together' => true,
                        'with' => array(
                            'roles'=> array(
                                'having' => "roles.role IN ($userRoles)"
                                ))
                    )
            );
        $count = $model->count($criteria);
        $result= $model->findAll($criteria);

While the findAll() method returns only 3 records (which is good) the count() method returns 13 which is the total number of records in the table represented by the $model

I've enabled query logging in MySQL and I found out that the two query generated by Yii is completely different

SELECT `t`.`id` AS `t0_c0`,
       `t`.`name` AS `t0_c1`,
       `t`.`description` AS `t0_c2`,
       `t`.`enabled` AS `t0_c3`,
       `t`.`issuegroup_id` AS `t0_c4`,
       `t`.`role_id_exec` AS `t0_c5`,
       `t`.`require_attachment` AS `t0_c6`,
       `roles`.`id` AS `t1_c0`,
       `roles`.`role` AS `t1_c1`,
       `roles`.`enabled` AS `t1_c2`,
       `roles`.`description` AS `t1_c3`
FROM `issuetype` `t`
       LEFT OUTER JOIN `role_has_issuetype` `roles_roles` ON
         (`t`.`id`=`roles_roles`.`issuetype_id`)
       LEFT OUTER JOIN `role` `roles` ON
         (`roles`.`id`=`roles_roles`.`role_id`)
HAVING (roles.role IN ('user'))
LIMIT 100

The other query:

SELECT COUNT(DISTINCT `t`.`id`)
FROM `issuetype` `t`
LEFT OUTER JOIN `role_has_issuetype` `roles_roles` ON
    (`t`.`id`=`roles_roles`.`issuetype_id`)
LEFT OUTER JOIN `role` `roles` ON
    (`roles`.`id`=`roles_roles`.`role_id`)

Is this the normal behavior for the findAll() and count() methods or did I do something I shouldn't have done or is this a bug in Yii?

And how to get the actual count of the records properly?

count($model->findAll($criteria)) seems to be working fine but is this the correct solution or is it just a workaround?

(From a performance viewpoint I think it might be better than the actual count() because I'm running the same query twice which is cached by the MySQL server)

UPDATE: I've asked the same question on GitHub and Paul Klimov kindly pointed out that it is unnecessary for the 'having' and 'group' clauses to be in the joined table and it is perfectly OK to move it out of the 'with' see it here: https://github.com/yiisoft/yii/issues/3297

Upvotes: 1

Views: 8690

Answers (2)

Dem
Dem

Reputation: 1

You need to clone your model before count() or findAll() method applying:

$result= $model->findAll($criteria);
$modelClone = clone $model;
$count = $model->count($criteria);

Upvotes: 0

darkheir
darkheir

Reputation: 8950

Yii had some problem with Having criteria while using count method from ActiveRecord, but it is fixed in newer Yii versions: https://github.com/yiisoft/yii/pull/2167

Upvotes: 2

Related Questions