如何在 yii 框架模型中转换此查询 [英] how to convert this query in yii framework model

问题描述

SELECT u.id, u.username, u.score, 
(SELECT COUNT(ownerId) FROM post p WHERE p.ownerId = u.id) AS totalPost 
FROM users u 
ORDER BY u.score DESC, totalPost DESC LIMIT 10

推荐答案

$sql = "SELECT u.id, u.username, u.score, ".
   "(SELECT COUNT(ownerId) FROM post p WHERE p.ownerId = u.id) AS totalPost ".
   "FROM users u ".
   "ORDER BY u.score DESC, totalPost DESC ".
   "LIMIT 10";
$command=Yii::app()->db->createCommand($sql);
$results=$command->query();

或者如果你有一个 User 模型(我认为这会起作用 - 我没有测试其中任何一个；)

or if you have a User model (I think this will work - I didn't test either of these ;)

$criteria = new CDbCriteria();
$criteria->select = "t.id, t.username, t.score, (SELECT COUNT(ownerId) FROM post p WHERE p.ownerId = t.id) AS totalPost";
$criteria->order = "u.score DESC, totalPost DESC";
$criteria->limit = "10";
$results = User::model()->findAll($criteria); // this returns an array of User models

这篇关于如何在 yii 框架模型中转换此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！