Yii: Select 20 last entries order by id ASC

Question

I would like to get the 20 last entries of my table but order by ascending id.

In Sql it's not very complicated:

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:

Comment::model()->findAll($criteria)

But i really don't know what I should put in my CDbCriteria!

Answer 1

$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:

"order" => "id DESC, created ASC"

Scrap that secondary ordering, but why not just use array reverse?

$models = array_reverse($models);

Answer 2

There is a way without using array_reverse, if you think of using this 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 :

$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.