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.
Advertisement
Answer
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" }