时间戳

首页 > 解决方案 > 如何在 CodeIgniter3 上获取下一个和上一个 id？

php - 如何在 CodeIgniter3 上获取下一个和上一个 id？

问题描述

我有以下数据库。

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

如您所见，ID 缺少一颗牙齿。

因此，您无法通过简单地在当前 ID 上加或减 1 来获得有效的上一个或下一个 ID

在 SQL 中，要获得有效的来回 ID，我会编写以下代码。

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

我想通过 CodeIgniter3 样式来获取这种记录检索。如何将其写为 codeigniter3 活动记录？

@sajushko

谢谢回复。我已经确认它按我的预期工作。

但是我想知道的是如何在CodeIgniter3的ActiveRecord而不是SQL语句中编写它。

具体来说，是这样的。

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

由于将 SQL 语句直接写入业务逻辑是导致 SQL 注入的原因，因此我们希望使用包装函数来编写它们。

标签： phpmysqlcodeigniter-3

解决方案

好吧，我想出了如何将其编写为 codeigniter3 样式，就像这样。

请让我知道任何其他比我更酷的代码。

    /**
     * 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"
    }

推荐阅读