How to get next and previous id on CodeIgniter3?

Question

I have the following DB.

+----+------+
| id | name |
+----+------+
|  1 | aaaa |
|  2 | bbbb |
|  4 | dddd |
|  7 | gggg |
+----+------+

As you can see, the ID is missing a tooth.

so you cannot get a valid previous or next ID by simply adding or subtracting 1 to the current ID

In SQL, to get a valid back and forth ID, I would write the following.

SELECT
  (SELECT id FROM topics WHERE id < tmp.id ORDER BY id DESC LIMIT 1) AS prev_id,
  (SELECT id FROM topics WHERE id > tmp.id ORDER BY id ASC LIMIT 1) AS next_id
FROM
  topics AS tmp
WHERE
  id = 1
;

+---------+---------+
| prev_id | next_id |
+---------+---------+
| NULL    |       2 |
+---------+---------+

id = 2

+---------+---------+
| prev_id | next_id |
+---------+---------+
|       1 |       4 |
+---------+---------+

id = 4

+---------+---------+
| prev_id | next_id |
+---------+---------+
|       2 |       7 |
+---------+---------+

id = 7

+---------+---------+
| prev_id | next_id |
+---------+---------+
|       4 | NULL    |
+---------+---------+

id = 999

empty set

I want to get this kind of record retrieval in the way by CodeIgniter3 styles. How can I write it as codeigniter3 active record?

@sajushko

Thanks for the reply. I have confirmed that it works as I intended.

But what I want to know is how to write it in ActiveRecord of CodeIgniter3 instead of SQL statements.

Specifically, it's something like this.

    public function getBothIds($id)
    {
        $tableName = "topics";
        $columnName = "id";
        $asColumnNamePrev = "prev_" . $columnName;
        $asColumnNameNext = "next_" . $columnName;

        $query = $this->db
                      ->select_min($columnName, $asColumnNamePrev) // how to write where inside of select_min?
                      ->select_max($columnName, $asColumnNameNext) // how to write where inside of select_max?
                      ->where($columnName, $id)
                      ->get($tableName)
                      ->row();
        return $query;
    }

Since writing SQL statements directly into business logic is a cause of SQL injection, we want to write them using a wrapper function.

Answer 1

well, I figured out how to write it as codeigniter3 style, like this.

Please let me know any other code more cool than I.

    /**
     * Get Prev And Next Id
     *
     * @param  integer|string $id e.g. 1
     * @return stdClass|null
     */
    public function getNeighborIds($id)
    {
       /**
        * SELECT
        *     (SELECT MAX(`id`) FROM `topics` WHERE `id` < `tmp`.`id`) AS `prev_id`,
        *     (SELECT MIN(`id`) FROM `topics` WHERE `id` > `tmp`.`id`) AS `next_id`
        * FROM
        *     `topics` AS `tmp`
        * WHERE
        *     `id` = 10;
        */

        $tableName = $this->tableName;
        $columnName = $this->primaryKey;
        $asColumnNamePrev = "prev_" . $columnName;
        $asColumnNameNext = "next_" . $columnName;

        $queryMin = '(SELECT MAX(' . $columnName . ') FROM ' . $tableName . ' WHERE ' . $columnName . ' < ' . 'tmp.' . $columnName . ') AS ' . $asColumnNamePrev;
        $queryMax = '(SELECT MIN(' . $columnName . ') FROM ' . $tableName . ' WHERE ' . $columnName . ' > ' . 'tmp.' . $columnName . ') AS ' . $asColumnNameNext;

        $query = $this->db
                      ->select($queryMin)
                      ->select($queryMax)
                      ->where($columnName, $id)
                      ->get($tableName . ' AS tmp')
                      ->row();
        return $query; // e.g. $query->prev_id "8" $query->next_id "11"
    }