4

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!

darkheir
  • 8,844
  • 6
  • 45
  • 66
  • Yeah I know kind of well Yii, but I don't know how to formulate the sql request in a cactiverecord way. The main problem that i can't formulate is that the frome clause is a request and I really don't know how to put it in CActiveRecord style. I'm not the only one with this problem and until now nobody answered it successfully: http://www.yiiframework.com/forum/index.php/topic/11312-active-record-last-entries/page__p__55531__hl__la+t+entrie+a+cending#entry55531 – darkheir Oct 17 '12 at 19:42
  • 1
    I read again the documentation and I didn't find what could be helping me! The only stuff that i see right now is do: $criteria = new CDbCriteria(); $criteria->limit = 20; $criteria->order = 'id DESC'; $models = array_reverse(Comment::model()->finAll($criteria)); But I would like to avoid the use of array_reverse() – darkheir Oct 17 '12 at 19:51
  • whats the problem with $criteria->order = 'id ASC'; ? – Asgaroth Oct 17 '12 at 20:01
  • With $criteria->order = 'id ASC'; I will have the 20 first entries from comments table. I want the 20 last, but ordered by ascending value! – darkheir Oct 17 '12 at 20:08

3 Answers3

10
$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);
Brett Gregson
  • 5,867
  • 3
  • 42
  • 60
  • 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 – darkheir Oct 17 '12 at 20:10
  • is the id attribute not your primary key? why would 2 records have the same id? – Brett Gregson Oct 17 '12 at 20:14
  • sorry, my bad, mis read your response. yes you are correct. why do you not want to use array_reverse? – Brett Gregson Oct 17 '12 at 20:18
  • 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! – darkheir Oct 17 '12 at 20:36
  • 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 – Brett Gregson Oct 17 '12 at 20:57
4

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.

bool.dev
  • 17,508
  • 5
  • 69
  • 93
  • 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? – darkheir Oct 18 '12 at 07:34
  • 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 Oct 18 '12 at 07:48
  • 1
    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. – bool.dev Oct 18 '12 at 09:15
  • 1
    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 Oct 18 '12 at 10:21
  • 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! – darkheir Oct 18 '12 at 12:35
2

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);
Community
  • 1
  • 1
Vladimir Posvistelik
  • 3,843
  • 24
  • 28