[FIXED] Codeigniter limit and offset pagination not working in descending order

Issue

Pagination worked fine in ASC order but I just can't get it to work in DESC order.

I have 10 test videos in my database with ids from 1-10, in the first batch I want to get the last 5 videos (ids: 10,9,8,7,6) and in the next batch the remaining ones (id:5,4,3,2,1).

The problem is that I always get empty results when using DESC order, here is my code:

    $limit = 5;

    $this->db->select('*');
    $this->db->from('participant_videos');
    $this->db->order_by('id','DESC');
    $this->db->limit($limit,$index); // currently index is 10
    $query=$this->db->get();
    
    $videos = $query->result_array(); // empty array
    echo $this->db->last_query();
    // QUERY: SELECT * FROM `participant_videos` ORDER BY `id` DESC LIMIT 10, 5

    return [
        'videos' => $videos,
        'index' => empty($videos) ? 0 : $index - $limit,
    ]; 

First time the index is determined like this:

 public function get_latest_video () {
    
    $video = $this->db->select("*")->limit(1)->order_by('id',"DESC")->get("participant_videos")->row_array();
    
    return !empty($video['id']) ? $video['id'] : null; // index (10)

}

This is my table structure and content:

== Table structure for table participant_videos

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|participant_id|int(11)|No|
|video_url|varchar(255)|No|
|video_type|varchar(255)|No|video/mp4
== Dumping data for table participant_videos

|2|2|/uploads/2-6138aef5a0717.mp4|video/mp4
|3|3|/uploads/3-6138b09449800.mp4|video/mp4
|4|4|/uploads/4-6138b0c3b1965.mp4|video/mp4
|5|5|/uploads/5-6138b0efa7aa1.mp4|video/mp4
|6|6|/uploads/6-6138b10667680.mp4|video/mp4
|7|7|/uploads/7-6138b154084a4.mp4|video/mp4
|8|8|/uploads/8-6138b1779bee0.mp4|video/mp4
|9|9|/uploads/9-613b4bc1e1d58.mp4|video/mp4
|10|16|/uploads/16-613b57a76c696.mp4|video/mp4

Solution

It turns out that offset "switches" sides when in DESC order, so instead of using biggest id as offset:

SELECT * FROM participant_videos ORDER BY id DESC LIMIT 10, 5

I had to change my offset to 0 to start from the biggest id:

SELECT * FROM participant_videos ORDER BY id DESC LIMIT 0, 5

This way I would recive the last 5 videos.

Answered By - failedCoder