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!

Solution 1

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.

Solution 2

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

Solution 3

UPD:

Please note, that in general, some other solutions are better than mine.

Using offset can decrease performance of your queries. See: http://www.slideshare.net/Eweaver/efficient-pagination-using-mysql and Why does MYSQL higher LIMIT offset slow the query down?

So, when the number of Comments will increase, you can get performance degradation.

---

What about using offset feature?

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

Comments

[darkheir]

I think that in "id DESC, created ASC" the "created ASC" will only be used for entries with the same id. We should read it as: We sort by id DESC and then if they have the same by created ASC

[Brett Gregson]

is the id attribute not your primary key? why would 2 records have the same id?

[Brett Gregson]

sorry, my bad, mis read your response. yes you are correct. why do you not want to use array_reverse?

[darkheir]

I think that's I will end up doing! I wasn't found of this because of performances, but with only 20 entries it shouldn't be that bad!

[Brett Gregson]

yeah i think array_reverse is the way to go, 20 records will definitely not be an issue unless the table structure has loads and loads of columns

[bool.dev]

offset is a good idea, but it might not work in all cases (not sure)

[darkheir]

It seems to be solving the issue, but do you know how it performs in terms of performance? There are 3 sql requests so won't it be a little long?

[bool.dev]

i can speculate: the inner most query is where it might take time, if your table is huge. The second level will definitely be extremely fast, the third(outermost) level again could take time depending on the size of table. But again since this is an id field i don't think it'll take long. You'll have to test it out, and compare with your initial method of array_reverse, which i thought was also a good option, but since you asked of another way, this was it. In the mean time i'll also try and get back to you with test results.

[bool.dev]

i kept increasing the seed table(went upto 10,000 records), and checked results: 1. At 1000 records- multi-select = 0.0031068325042725, array_reverse = 0.00077199935913086, 2. At 10,000 records- multi-select = 0.020848989486694, arrayreverse = 0.00089120864868164. These are microtime differences, as you can see the multiselect degrades, while the array_reverse changes only because the db query is taking longer. So ofcourse you should go with your old method. Can't think of an alternate query, which will not be effected by db size.

[darkheir]

Thnaks for the benchmark! I'll stick with array_reverse then, but your answer is still responding to my question since it shows how to perform the request using CActiveRecord!

[darkheir]

Yeah u r right, I didn't think about it! So tonight I'm goign to try findBySql and array_reverse to see which one seems better!