get next & previous id record in database on Yii

Question

I need next & previous id record in database on Yii framework to make navigation buttons next and back ?

Ulugov · Answer 1 · 2015-07-24T14:52:02.703

12

I added following functions in my model in Yii2:

public function getNext() {
    $next = $this->find()->where(['>', 'id', $this->id])->one();
    return $next;
}

public function getPrev() {
    $prev = $this->find()->where(['<', 'id', $this->id])->orderBy('id desc')->one();
    return $prev;
}

edited Jul 24 '15 at 14:52

answered Jul 22 '15 at 14:50

Ulugov

347
4
9

2

By far the best answer. Most others use ->all(), which is a waste of resources IMO. – vanarie Feb 04 '18 at 07:50

score 5 · Accepted Answer · edited Nov 14 '16 at 12:06

5

I made a function to get those ids your looking for. I suggest you to declare it in the model:

public static function getNextOrPrevId($currentId, $nextOrPrev)
{
    $records=NULL;
    if($nextOrPrev == "prev")
       $order="id DESC";
    if($nextOrPrev == "next")
       $order="id ASC";

    $records=YourModel::model()->findAll(
       array('select'=>'id', 'order'=>$order)
       );

    foreach($records as $i=>$r)
       if($r->id == $currentId)
          return isset($records[$i+1]->id) ? $records[$i+1]->id : NULL;

    return NULL;
}

So to use it all you have to do do is this:

YourModel::getNextOrPrevId($id /*(current id)*/, "prev" /*(or "next")*/);

It will return the corresponding id of the next or previous record.

I didn't test it, so give it a try and if something goes wrong please let me know.

edited Nov 14 '16 at 12:06

Nikolas Evers

159
9

answered Jan 15 '12 at 23:40

Alfredo Castaneda Garcia

1,661
9
17

" class="dBlock left">Next – Abudayah Jan 16 '12 at 08:04
and I write that function in PhotoController – Abudayah Jan 16 '12 at 08:06
Fatal error: Call to undefined method Photo::getPrevOrNextId() – Abudayah Jan 16 '12 at 08:12
1

Since you declare the function in the controller, of course that function isn't in the model, Photo hasn't a function called getPrevOrNextId(). So just move the function to the model. – Alfredo Castaneda Garcia Jan 16 '12 at 08:16
Or trying calling it as PhotoController::getPrevOrNextId(); – Alfredo Castaneda Garcia Jan 16 '12 at 08:18
You must replace the assumptions i did of your column name with the actual names. So i supposed the id column of you table was called just "id", if that's not the case, then replace it whit the real name. – Alfredo Castaneda Garcia Jan 16 '12 at 08:27
yes it's "id" but when I repalce $id with number like this: i have this massage: "include(YourModel.php) [function.include]: failed to open stream: No such file or directory" – Abudayah Jan 16 '12 at 08:31
or: id, "next"); ?> – Abudayah Jan 16 '12 at 08:33
I used YourModel because i didn't know the actual name of the model. So, now that I know it, I can tell you that the correct statement is $records=Photo::model()->findAll(... etc – Alfredo Castaneda Garcia Jan 16 '12 at 08:33
OhoHohooho sorry maaaan i forget that in model code.. sorry I am junior – Abudayah Jan 16 '12 at 08:40
getNextOrPrevId insted of getPrevOrNextId :) – open-ecommerce.org Sep 04 '14 at 00:07
This is performance killer. If you have one million records in your database, this method will load them all, iterate thorough them and then return only one record. This is incredible waste of memory and CPU - database engine usually do such task much more efficient. – rob006 Oct 11 '18 at 09:13

score 2 · Answer 3 · answered Sep 22 '15 at 19:20

Taking the original answer and adapting it for Yii2 with a little clean up:

/**
 * [nextOrPrev description]
 * @source http://stackoverflow.com/questions/8872101/get-next-previous-id-record-in-database-on-yii
 * @param  integer $currentId [description]
 * @param  string  $nextOrPrev  [description]
 * @return integer         [description]
 */
public static function nextOrPrev($currentId, $nextOrPrev = 'next')
{
    $order   = ($nextOrPrev == 'next') ? 'id ASC' : 'id DESC';
    $records = \namespace\path\Model::find()->orderBy($order)->all();

    foreach ($records as $i => $r) {

       if ($r->id == $currentId) {
          return ($records[$i+1]->id ? $records[$i+1]->id : NULL);
       }

    }

    return false;
}

score 2 · Answer 4 · answered Jan 15 '12 at 18:34

Make a private var that is used to pass info to other functions.

In Model:

class Model1 .....
{
   ...
   private _prevId = null;
   private _nextId = null;
   ...

   public function afterFind()  //this function will be called after your every find call
   {
    //find/calculate/set $this->_prevId;
    //find/calculate/set $this->_nextId;
   }

   public function getPrevId() {
      return $this->prevId;
   }

   public function getNextId() {
      return $this->nextId;
   }

}

Check the code generated in the ViewDetal link and modify for the Prev/Net links in the _view file using

$model(or $data)->prevId/nextId

in the array('id'=>#) section.

dearmisterrobot · Answer 5 · 2016-12-22T02:08:08.570

My implementation is based on SearchModel.

Controller:

public function actionView($id)
{
    // ... some code before

    // Get prev and next orders
    // Setup search model
    $searchModel = new OrderSearch();
    $orderSearch = \yii\helpers\Json::decode(Yii::$app->getRequest()->getCookies()->getValue('s-' . Yii::$app->user->identity->id));
    $params = [];
    if (!empty($orderSearch)){
        $params['OrderSearch'] = $orderSearch;
    }
    $dataProvider = $searchModel->search($params);
    $sort = $dataProvider->getSort();
    $sort->defaultOrder = ['created' => SORT_DESC];
    $dataProvider->setSort($sort);

    // Get page number by searching current ID key in models
    $pageNum = array_search($id, array_column($dataProvider->getModels(), 'id'));
    $count = $dataProvider->getCount();
    $dataProvider->pagination->pageSize = 1;

    $orderPrev = $orderNext = null;
    if ($pageNum > 0) {
        $dataProvider->pagination->setPage($pageNum - 1);
        $dataProvider->refresh();
        $orderPrev = $dataProvider->getModels()[0];
    }
    if ($pageNum < $count) {
        $dataProvider->pagination->setPage($pageNum + 1);
        $dataProvider->refresh();
        $orderNext = $dataProvider->getModels()[0];
    }
    // ... some code after
}

OrderSearch:

public function search($params)
{
    // Set cookie with search params
    Yii::$app->response->cookies->add(new \yii\web\Cookie([
        'name' => 's-' . Yii::$app->user->identity->id,
        'value' => \yii\helpers\Json::encode($params['OrderSearch']),
        'expire' => 2147483647,
    ]));

    // ... search model code here ...
}

PS: be sure if you can use array_column for array of objects. This works good in PHP 7+ but in lower versions you got to extract id by yourself. Maybe it's good idea to use array_walk or array_filter in PHP 5.4+

score 0 · Answer 6 · answered Mar 14 '22 at 17:03

Full implemenentation with performance improvement by using DB engine/optimization (when id acts as primary key):

Model:

  public static function getNextPrevId($currentId)
    {
        $queryprev = new Query();
        $queryprev->select('max(id)')->from(self::tableName())->where('id<:id',['id'=>$currentId]);
        $querynext = new Query();
        $querynext->select('min(id)')->from(self::tableName())->where('id>:id',['id'=>$currentId]);

        return [ $queryprev->scalar(), $querynext->scalar()];
        
    }

Controller:

public function actionView($id) {
    return $this->render('view', [
        'model' => $this->findModel($id),
        'nextprev' => YourModel::getNextPrevId($id),
    ]);
}

View:

<?= !is_null($nextprev[0]) ? Html::a('⇦', ['view', 'id' => $nextprev[0]], ['class' => 'btn btn-primary']) : '' ?>
<?= !is_null($nextprev[1]) ? Html::a('⇨', ['view', 'id' => $nextprev[1]], ['class' => 'btn btn-primary']) : '' ?>

DrBorrow · Answer 7 · 2016-09-07T21:43:04.350

The previous solutions are problematic when you get the the first or last record and they are making multiple calls to the database. Here is my working solution which operates on one query, handles end-of-table and disables the buttons at end-of-table:

Within the model:

public static function NextOrPrev($currentId)
{
    $records = <Table>::find()->orderBy('id DESC')->all();

    foreach ($records as $i => $record) {
        if ($record->id == $currentId) {
            $next = isset($records[$i - 1]->id)?$records[$i - 1]->id:null;
            $prev = isset($records[$i + 1]->id)?$records[$i + 1]->id:null;
            break;
        }
    }
    return ['next'=>$next, 'prev'=>$prev];
}

Within the controller:

     public function actionView($id)
{
    $index = <modelName>::nextOrPrev($id);
    $nextID = $index['next'];
    $disableNext = ($nextID===null)?'disabled':null;
    $prevID = $index['prev'];
    $disablePrev = ($prevID===null)?'disabled':null;

    // usual detail-view model
    $model = $this->findModel($id);

    return $this->render('view', [
        'model' => $model, 
        'nextID'=>$nextID,
        'prevID'=>$prevID,
        'disableNext'=>$disableNext,
        'disablePrev'=>$disablePrev,
    ]);
}

Within the view:

<?= Html::a('Next', ['view', 'id' => $nextID], ['class' => 'btn btn-primary r-align btn-sm '.$disableNext]) ?>
<?= Html::a('Prev', ['view', 'id' => $prevID], ['class' => 'btn btn-primary r-align btn-sm '.$disablePrev]) ?>

get next & previous id record in database on Yii

7 Answers7

Linked