php - Codeigniter 4：如何使用构建器类或什至使用常规查询获取一系列下一行和上一行

Question

这是我当前的查询，它根据 URL($qs) 中的参数检索项目：我想获取项目的前一行和下一行中的 2 或 3 行。

    public function show_questions($qs = false)
     {
      $db = \Config\Database::connect();
      $builder->select('*');
      $builder->where('question_id ='. $qs);
      $builder->join('segments_en', 'questions_en.tape = segments_en.tape');                
      $query = $builder->get();
      return $query->getResultArray();                  
    }

标签： phpmysqlcodeigniter-4

Answer 1

我将表名( movie )、id 列名( Movie_ID ) 和Limit ( 2 ) 分隔到变量中，因为我使用了一个示例表（带有要测试的电影）我附上了结果

public function show_questions($qs = false)
{
    $sql = array();
    $table = "questions_en";
    $joined_table = "segments_en";
    $id_key = "question_id";
    $limit = 2;

    $db = \Config\Database::connect();
    $builder = $db->table($table);

    // Select previous records
    $builder->select('*');
    $builder->join($table, $table.'.tape = '.$joined_table.'.tape');
    $builder->where($id_key.' >'. $qs);
    $builder->orderBy($id_key, 'ASC');
    $builder->limit($limit);
    $sql[] = '('. $builder->getCompiledSelect() .')';


    // Select record which is identified by ID ($qs)
    $builder->select('*');
    $builder->join($table, $table.'.tape = '.$joined_table.'.tape');
    $builder->where($id_key, $qs);
    $sql[] = '('. $builder->getCompiledSelect() .')';

    // Select next records
    $builder->select('*');
    $builder->join($table, $table.'.tape = '.$joined_table.'.tape');
    $builder->where($id_key.' <'. $qs);
    $builder->orderBy($id_key, 'DESC');
    $builder->limit($limit);
    $sql[] = '('. $builder->getCompiledSelect() .')';

    $sql_query = implode('
UNION
', $sql);

    $query_string = "SELECT * FROM ($sql_query) questions ORDER BY ".$id_key." ASC";

    $query = $db->query($query_string);
    //var_dump($query->getResultArray());
    return $query->getResultArray();
}