[rei]

I have been working on a CGridView that needs to go to specific page automatically based on URL. For example if the URL is:

test.com/index.php?r=material/admin&id=20000

then the application will need to set CGridView's page to where item with ID = 20,000 exists. So, if it is in page 200, then user will be showed page 200 of CGridView when going to that URL.

What I have tried:
1. Find item in CGridView's data provider.
2. Calculate page number of that item (where it exists).
3. Set page number of CGridView's data provider.

$dataProvider->pagination = false;

// find ID in current data provider (in the example ID = 20,000)
$index = array_search($id, $dataProvider->keys);
        
// calculate the correct page number (maximum items in 1 page = 10)
if ($index < 10)
    $page = 0;
else
    $page = ceil(($index+1)/10) - 1;

$dataProvider->pagination->currentPage = $page;

Although working correctly, that approach takes too much memory (that raises Memory Exhausted error) when it deals with a lot of data. Is there a more efficient way to get page number of specific item?

[KonApaz]

Hi rei

You could make a query with createCommand (http://www.yiiframew...SqlDataProvider)
with same pagination sort order (on select set only id's for performance) and by looping get the order of specific item, then use it to set current page
with $page = ceil(($indexOrder+1)/10) - 1;

There is even more performance for this technique, so let me know if you want it

[rei]

KonApaz, on 17 November 2012 - 02:54 AM, said:

You could make a query with createCommand (http://www.yiiframew...SqlDataProvider)
with same pagination sort order (on select set only id's for performance) and by looping get the order of specific item, then use it to set current page
with $page = ceil(($indexOrder+1)/10) - 1;

There is even more performance for this technique, so let me know if you want it

Thanks for your response, KonApaz, but I don't understand your explanation. Do you mean I need to create another data provider (SQLDataProvider) to get the page number? Could you please elaborate on this?

[KonApaz]

rei, on 17 November 2012 - 07:16 AM, said:

Thanks for your response, KonApaz, but I don't understand your explanation. Do you mean I need to create another data provider (SQLDataProvider) to get the page number? Could you please elaborate on this?

No, just use the same sort order of dataprovider, so create another data query to find the position of desired item and set the current page pagination as I suggest you

[rei]

KonApaz, on 17 November 2012 - 07:59 AM, said:

No, just use the same sort order of dataprovider, so create another data query to find the position of desired item and set the current page pagination as I suggest you

How can I find the position (page number) of item by query? Some code examples would be very helpful.

[KonApaz]

rei, on 17 November 2012 - 08:22 AM, said:

How can I find the position (page number) of item by query? Some code examples would be very helpful.

$SQL="select id from your_table order by <your_field> DESC"
$connection=Yii::app()->db;
command=$connection->createCommand($sql);
$dataReader=$command->query();

$indexOrder=0;
foreach($dataReader as $row) {
if ($row->ID==<your_desired_ID>) break;
$indexOrder++;
}

$page = ceil(($indexOrder+1)/10) - 1;
$dataProvider->pagination->currentPage = $page;

[rei]

I use CDbCriteria to do filtering for data providers because there are many conditions and complicated flows that need to be handled. It would be a tedious process if I have to rewrite all of them in SQL format so that I ended up using:

// get criteria & reduce column selection
$criteria = $dataProvider->getCriteria();
$criteria->select = 't.id';
$criteria->order = $dataProvider->getSort()->getOrderBy($criteria);

// requery data using CDbCommand
$tableSchema = $dataProvider->model->getTableSchema();
$command = $dataProvider->model->getCommandBuilder()->createFindCommand($tableSchema, $criteria);
$items = $command->query();

// find item in query result
$index = 0;
foreach ($items as $item) 
{ 
    if (strcmp($item['id'], $id) == 0)
        break;    
    $index++;
}

// set page to first page if item is not found
if ($index == count($items))
    $page = 0;
else
    $page = ceil(($index+1)/10) - 1;

return $page;

And it works well now. Thank you for your help on this!

[KonApaz]

rei, on 18 November 2012 - 09:14 AM, said:

I use CDbCriteria to do filtering for data providers because there are many conditions and complicated flows that need to be handled. It would be a tedious process if I have to rewrite all of them in SQL format so that I ended up using:

// get criteria & reduce column selection
$criteria = $dataProvider->getCriteria();
$criteria->select = 't.id';
$criteria->order = $dataProvider->getSort()->getOrderBy($criteria);

// requery data using CDbCommand
$tableSchema = $dataProvider->model->getTableSchema();
$command = $dataProvider->model->getCommandBuilder()->createFindCommand($tableSchema, $criteria);
$items = $command->query();

// find item in query result
$index = 0;
foreach ($items as $item) 
{ 
    if (strcmp($item['id'], $id) == 0)
        break;    
    $index++;
}

// set page to first page if item is not found
if ($index == count($items))
    $page = 0;
else
    $page = ceil(($index+1)/10) - 1;

return $page;

And it works well now. Thank you for your help on this!

Good approach rei, more rubust and dynamically