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 ?

Answer 1

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']) : '' ?>

Answer 2

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+

Answer 3

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.

Answer 4

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]) ?>

Answer 5

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;
}

Answer 6

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;
}

Answer 7

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.