Yii 模型 findAll() 和 count() 返回不同数量的结果 [英] Yii model findAll() and count() return different number of results

问题描述

更新:见问题的结尾

我正在与 Yii 合作(尤其是 RESTFullYii，但我怀疑这与问题相关)

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

模型有一个 CDbCriteria:

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

虽然 findAll() 方法只返回 3 条记录(这很好)，但 count() 方法返回 13，这是表中记录的总数通过 $model

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

我在 MySQL 中启用了查询日志，发现 Yii 生成的两个查询完全不同

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

另一个查询:

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`)

这是 findAll() 和 count() 方法的正常行为还是我做了一些我不应该做的事情，或者这是 Yii 中的一个错误?

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)) 似乎工作正常，但这是正确的解决方案还是只是一种解决方法?

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

(从性能的角度来看，我认为它可能比实际的 count() 更好，因为我运行了两次由 MySQL 服务器缓存的相同查询)

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

更新:我在 GitHub 上问过同样的问题，Paul Klimov 好心地指出，'have' 和 'group' 子句没有必要出现在连接表中，将它移出 'with' 是完全可以的，请参阅它在这里:https://github.com/yiisoft/yii/issues/3297

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

推荐答案

Yii 在使用 ActiveRecordcount 方法时，在使用 Having 条件时遇到了一些问题>，但在较新的 Yii 版本中已修复:https://github.com/yiisoft/yii/拉/2167

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

这篇关于Yii 模型 findAll() 和 count() 返回不同数量的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！