limit doesn't work with ActiveRecord

Question

I have some problems with the limit using active record.

I've created a dataProvider with a limit of 5:

$dataProvider = new ActiveDataProvider([
        'query' => Devicesdb::find()
            ->joinWith('score')
            ->where('devices.devicetype = :deviceType', [':deviceType' => $device])
            ->orderBy(['score' => SORT_DESC])
            ->limit(5),
        'totalCount' => 5,
    ]);

And this is the resultant query in debug panel:

SELECT `devicesdb`.* 
FROM `devicesdb` 
LEFT JOIN `devices` 
ON `devicesdb`.`id` = `devices`.`id` 
WHERE devices.devicetype = 'phone' 
ORDER BY `score` 
DESC LIMIT 20

The query is fine , and retourns me the data as I want, but I only want 5 items, not 20.

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

First of all totalCount is a property of Pagination, not ActiveDataProvider. You need to nest it inside of a pagination configurational array. But this is not how you achieve this.

Because you don't want the pagination to appear you can disable it by passing false and now limit will be taken from query (otherwise it's ignored and calculated differently, you can see this related question):

$dataProvider = new ActiveDataProvider([
    'query' => Devicesdb::find()
        ->joinWith('score')
        ->where('devices.devicetype = :deviceType', [':deviceType' => $device])
        ->orderBy(['score' => SORT_DESC])
        ->limit(5),
        'pagination' => false,
    ]);
]);

One more thing - you don't have to write params manually, see this question for explanation and better understanding. So where part of the query can be reduced to just:

->where(['devices.devicetype' => $device])

Also I recommend to refactor model name to just Device and use this to resolve duplicate names conflicts (if any) in SQL query:

->where(Device::tableName() . 'devicetype' => $device])

That way if this model related table name will changed in the future, you don't have to refactor your code.

edited May 23 '17 at 11:44

Community

1
1

answered Dec 03 '15 at 09:02

arogachev

31,868
6
105
113

But totalcount is a property of ActiveDataProvider i used to hide the the pagination bar. Anywhere I've tryed your code and doesn't work. Query still retourning 20 items. – Sageth Dec 03 '15 at 09:11
@Sageth I corrected the answer with more detailed explanation, check it. – arogachev Dec 03 '15 at 09:13
i've found a pretty simple solution thanks to you to give me and idea :D, check it, I want to know your opinion. – Sageth Dec 03 '15 at 09:20
@Sageth Sorry, I missed something. Corrected the answer once again, please check it. And by the way you can post your solution as an answer, not as an edit to question. – arogachev Dec 03 '15 at 09:21
1

I've posted the edit before saw your edit, and your code is smarter, thanks :D – Sageth Dec 03 '15 at 09:23
@Sageth I checked your solution, I suggested something similar before. But this doesn't hide the pagination bar in case of amount of models larger than limit (5). – arogachev Dec 03 '15 at 09:30

limit doesn't work with ActiveRecord

1 Answers1