$criteria->limit does not work

Archives Yii 1.0.x Discussions
1

Hi,

I have a problem with CDbCriteria,

I set limit to 10 records ($criteria->limit=10) but it seems not to work, I get all records, not 10.

Do You have any idea?

My admin action:




public function actionAdmin()

   $criteria=new CDbCriteria;

   $criteria->limit = 10;


   $pages=new CPagination(myModel::model()->count($criteria));		

   $pages->applyLimit($criteria);

   $pages->pageSize=5;


   $sort=new CSort('myModel');

   $sort->attributes=array(

       'myModel.id'=>'id',

       'myModel.name'=>'name',

       'relAuthor.login'=>'login',

   );

   $sort->defaultOrder = 'myModel.name';

   $sort->applyOrder($criteria);


   $elementsList=myModel::model()->with('relAuthor')->findAll($criteria);

   $this->render('admin',array(

      'elementsList'=>$elementsList,

      'pages'=>$pages,

      'sort'=>$sort,

   ));

}
2

Your pagination is slightly wrong:




<?php

$criteria = new CDbCriteria;

$pages = new CPagination(MyModel::model()->count($criteria));

$pages->pageSize = 1;

$pages->applyLimit($criteria);

$members = MyModel::model()->findAll($criteria);

$this->render('view', array('pages' => $pages));

This should produce the output your looking for.

Chris

3

Where is $criteria->limit ?

With your code I get one record per page.

When I have 100 records in table, I get 100 pages.

I want only 10 records from table: $criteria->limit = 10;

4

Just change the code of krak3n to $pages->pageSize = 10;

On the next line ($pages->applyLimit($criteria)) the LIMIT and OFFSET for $criteria is set.

There is no need to do this manually beforehand. When you initialize $pages as a Pagination object, it actually needs to know the number of all elements in you table, therefore you do not need any criteria there.

So the code becomes:




<?php

$criteria = new CDbCriteria;

$pages = new CPagination(MyModel::model()->count());

$pages->pageSize = 10;

$pages->applyLimit($criteria);

$members = MyModel::model()->findAll($criteria);

$this->render('view', array('pages' => $pages));
5

Oops, that’s what I get for copy and pasting lol :)

6

am I stupid?

ok, let’s say I Have 50 records in my table.

What happend when I use Your code @sluderitz:

  • I get 5 pages

  • Every page has 10 records

  • 5 * 10 = 50 records

or maybe I am wrong?

Now I know I have 50 records, and Yii shows me all of these records using 5 pages.

but I want to take only 10 records at all, I should get only one page (‘SELECT * FROM myTabel LIMIT 10’)!

7

If you want to get one page only, why do use pagination at all?

8

:-[ let’s say I want to take only 10 last records (the table has 50 records), and my pageSize is 5. That means I will get 2 pages (5 records each).

I do not want to see the 40 old records from my table.

9

I have the same problem

I think that

$pages->applyLimit($criteria);

applies the

$pages->pageSize to $criteria

overriding the

$criteria->limit

so the limit you have set doesn’t work.

10

I see. What about the following change:




$pages=new CPagination(max(10, myModel::model()->count($criteria)));
11

no, it doesn’t work.

12

Sorry, you have to use min() instead of max().

13

it doesn’t worh neither, but it doesn’t matter, I just wanted to know why




$criteria=new CDbCriteria;

$criteria->limit = 10;

doesen’t work…

I think Spyros is right,

$pages->applyLimit($criteria);

overrides $criteria->limit = 10;

Thanks for help!

14

Ok, I now see what you would like to do.

Here are the 2 problems with your code:

$criteria->limit = 10;

$pages=new CPagination(myModel::model()->count($criteria));

The SQL statement generated is:

SELECT count(*) FROM myModel LIMIT 10;

Since the count result is only one row the LIMIT 10 does not do anything.

So do solve that you need the approach suggested by pestaa

In your initial code line 5 and 6 should be swapped.

Pagination has a default page size of 10, you need to change it before you use applyLimit

So I think this could work:




public function actionAdmin() {

    $criteria=new CDbCriteria;   

    $pages=new CPagination(min(10, myModel::model()->count()));

    $pages->pageSize=5;       

    $pages->applyLimit($criteria);   

    $sort=new CSort('myModel');   

    $sort->attributes=array(       

        'myModel.id'=>'id',       

        'myModel.name'=>'name',       

        'relAuthor.login'=>'login',   

    );   

    $sort->defaultOrder = 'myModel.name';   

    $sort->applyOrder($criteria);   

    $elementsList=myModel::model()->with('relAuthor')->findAll($criteria);   

    $this->render('admin',array(

          'elementsList'=>$elementsList,

          'pages'=>$pages,

          'sort'=>$sort,   

    ));

}

And yes, applyLimit() does overwrite the criteria limit.

The CPagination code is nothing magic:




public function applyLimit($criteria)

{

    $criteria->limit=$this->pageSize;

    $criteria->offset=$this->currentPage*$this->pageSize;

}
15

ok, it works.

thanks again!

16

IMO, the method should be in the Criteria class. You can’t know from the code what it is “doing” without opening the underlying class.

The confusion discussed above is, imo, a result of unexpected, or obfuscated, behaviour in the current classes. I’ve wrapped these classes here to avoid this issue.

Instead, it should be something like:





$criteria->applyPageLimits($pages->getPageSize(), $pages->getCurrentPage());


class CDbCriteria {

    public function applyPageLimits($pagesize, $currentpage) {

        $this->limit = $pagesize;

        $this->offset = $currentpage * $pagesize;

    }

}

This isn’t the only place that this behaviour occurs. Have a look at CSort::ApplyOrder(), for example. Now, what’s that doing (rhetorical)?

In this case, the Criteria object isn’t changed until the last if-statement, so moving this around (and simplifying it) isn’t hard. (e.g.CSort::applyOrder() becomes CSort::getOrder(), and then update $criteria->order).