php Yii：按 id ASC 选择最后 20 个条目顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12941979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Yii: Select 20 last entries order by id ASC
提问by darkheir
I would like to get the 20 last entries of my table but order by ascending id.
我想获取表的最后 20 个条目，但按 id 升序排序。
In Sql it's not very complicated:
在 Sql 中它不是很复杂：
SELECT *
FROM (SELECT * FROM comments
WHERE postID='$id'
ORDER BY id DESC
LIMIT 20) t
ORDER BY id ASC;
But I would like to to it with my yii model like:
但我想用我的 yii 模型来做，比如：
Comment::model()->findAll($criteria)
But i really don't know what I should put in my CDbCriteria!
但我真的不知道我应该在我的 CDbCriteria 中放什么！
回答by Brett Gregson
$models = Comment::model()->findAll(array(
"condition" => "WHERE postID = '".$id."'",
"order" => "id DESC",
"limit" => 20,
));
Will get the last 20. And now you want to order that record set by id ASC correct? Is there not another field you can order by for a similar result (maybe a date or created field?) eg:
会得到最后的 20 个。现在你想按照 id ASC 设置的那个记录顺序正确吗？是否没有其他字段可以订购类似的结果（可能是日期或创建的字段？）例如：
"order" => "id DESC, created ASC"
Scrap that secondary ordering, but why not just use array reverse?
取消二级排序，但为什么不直接使用数组反向？
$models = array_reverse($models);
回答by bool.dev
There is a way without using array_reverse, if you think of using this sql:
有一种不使用的方法array_reverse，如果你想到使用这个sql：
SELECT * FROM `comments` `t`
WHERE id
in (SELECT id
FROM (SELECT id FROM comments Where postID = xyz ORDER BY id DESC LIMIT 20)
as q)
ORDER BY id ASC
which in criteria will become:
其标准将变为：
$criteria=new CDbCriteria();
$criteria->condition='id in (SELECT id FROM (SELECT id FROM comments Where postID='.$id.' ORDER BY id DESC LIMIT 20) as q)';
$criteria->order='id ASC';
Update:
更新：
With your original query, you could have also used findBySql:
对于您的原始查询，您还可以使用findBySql：
$sql='SELECT * FROM (SELECT * FROM comments WHERE postID= :postid ORDER BY id DESC LIMIT 20) q ORDER BY id ASC';
$params=array('postid'=>$id);
$comments=Comment::model()->findAllBySql($sql,$params);
The performance of this query was better than my previous query.
这个查询的性能比我之前的查询好。
回答by Vladimir Posvistelik
UPD:
更新：
Please note, that in general, some other solutions are better than mine.
请注意，一般来说，其他一些解决方案比我的要好。
Using offsetcan decrease performance of your queries.
See: http://www.slideshare.net/Eweaver/efficient-pagination-using-mysqland Why does MYSQL higher LIMIT offset slow the query down?
使用offset会降低查询的性能。请参阅：http: //www.slideshare.net/Eweaver/efficient-pagination-using-mysql和为什么 MYSQL 更高的 LIMIT 偏移会减慢查询速度？
So, when the number of Commentswill increase, you can get performance degradation.
因此，当数量Comments增加时，您可能会出现性能下降。
What about using offsetfeature?
使用offset功能怎么样？
$model = Comment::model();
$condition = 'postID =' . $id;
$limit = 20;
$totalItems = $model->count($condition);
$criteria = new CDbCriteria(array(
'condition' => $condition,
'order' => 'id ASC',
'limit' => $limit,
'offset' => $totalItems - $limit // if offset less, thah 0 - it starts from the beginning
));
$result = $model->findAll($criteria);
