如何在单条SQL/Laravel查询中获取当前及前后文章？

你之前的查询逻辑错误在于where id < 5 and id > 5——没有任何数据能同时满足这两个矛盾条件，自然查不出结果。完全可以用单条查询实现需求，无需拆分多条。

实现方案 #

1. SQL 查询写法 #

如果只需要上下篇的ID，同时获取当前文章信息：

SELECT
  -- 当前文章所有字段
  *,
  -- 上一篇最大ID
  (SELECT MAX(id) FROM posts WHERE id < 5) AS prev_id,
  -- 下一篇最小ID
  (SELECT MIN(id) FROM posts WHERE id > 5) AS next_id
FROM posts
WHERE id = 5;

如果需要上下篇的完整信息（而非仅ID），可以用左连接实现：

SELECT
  current.*,
  prev.id AS prev_id,
  prev.title AS prev_title,
  -- 按需添加其他上篇字段
  next.id AS next_id,
  next.title AS next_title
  -- 按需添加其他下篇字段
FROM posts current
LEFT JOIN posts prev ON prev.id = (SELECT MAX(id) FROM posts WHERE id < current.id)
LEFT JOIN posts next ON next.id = (SELECT MIN(id) FROM posts WHERE id > current.id)
WHERE current.id = 5;

2. Laravel Eloquent 写法 #

对应仅获取上下篇ID的场景：

$post = Post::select([
        '*',
        DB::raw('(SELECT MAX(id) FROM posts WHERE id < posts.id) AS prev_id'),
        DB::raw('(SELECT MIN(id) FROM posts WHERE id > posts.id) AS next_id')
    ])
    ->where('id', 5)
    ->first();

需要上下篇完整信息的场景：

$post = Post::query()
    ->select([
        'current.*',
        'prev.id as prev_id',
        'prev.title as prev_title',
        // 按需添加其他上篇字段
        'next.id as next_id',
        'next.title as next_title',
        // 按需添加其他下篇字段
    ])
    ->from('posts as current')
    ->leftJoin('posts as prev', function ($join) {
        $join->on('prev.id', '=', DB::raw('(SELECT MAX(id) FROM posts WHERE id < current.id)'));
    })
    ->leftJoin('posts as next', function ($join) {
        $join->on('next.id', '=', DB::raw('(SELECT MIN(id) FROM posts WHERE id > current.id)'));
    })
    ->where('current.id', 5)
    ->first();

说明 #

• 上述查询均为单条执行，无需拆分多次查询
• 如果不存在上/下篇，对应的字段会返回null，符合业务逻辑

内容的提问来源于stack exchange，提问作者Gabriel Edu