Laravel中按指定ReactionID统计数排序Posts结果的实现方法

数据库表结构 #

posts表 #

reactions表 #

post_reactions中间表 #

需求 #

需要实现按指定reaction_id的统计数对posts表结果进行排序，示例如下：

• 当按reactionId = 1排序时，结果应为：

[
  {
    "id": 1,
    "title": "Post A",
    "description": "Description A",
    "reaction_count": 2         // count of reactionId=1 is maximum here
  },
  {
    "id": 2,
    "title": "Post B",
    "description": "Description B",
    "reaction_count": 0         
  } 
]

• 当按reactionId = 2排序时，结果应为：

[
  {
    "id": 2,
    "title": "Post B",
    "description": "Description B",
    "reaction_count": 1      // count of reactionId=2 is maximum here 
  },
  {
    "id": 1,
    "title": "Post A",
    "description": "Description A",
    "reaction_count": 0         
  }
]

现有代码问题 #

你当前的子查询没有关联posts表的id和post_reactions的post_id，导致统计的是整个表中该reaction_id的总数量，而不是每个帖子对应的数量，所以排序结果会不正确。

解决方案 #

方法1：修正原有子查询（添加关联条件） #

在子查询中补充post_reactions.post_id = posts.id的关联逻辑，同时用参数绑定避免SQL注入：

// Controller
$reactionId = 1; // 指定的reaction_id

$posts = Post::select([
    '*',
    \DB::raw('(SELECT COUNT(*) FROM post_reactions WHERE post_reactions.post_id = posts.id AND post_reactions.reaction_id = ?) as reaction_count')
])
->setBindings([$reactionId])
->orderBy('reaction_count', 'desc')
->get();

方法2：左连接+聚合统计（性能更优） #

通过左连接post_reactions表，按帖子分组统计指定reaction_id的数量，适合数据量较大的场景：

// Controller
$reactionId = 1;

$posts = Post::leftJoin('post_reactions', function ($join) use ($reactionId) {
    $join->on('posts.id', '=', 'post_reactions.post_id')
         ->where('post_reactions.reaction_id', '=', $reactionId);
})
->select([
    'posts.*',
    \DB::raw('COUNT(post_reactions.id) as reaction_count')
])
->groupBy('posts.id')
->orderBy('reaction_count', 'desc')
->get();

方法3：利用Eloquent关联统计（推荐） #

在Post模型中定义自定义作用域，封装统计逻辑，代码更简洁易维护：

// Post Model
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

public function reactions(): BelongsToMany
{
    return $this->belongsToMany(Reaction::class)->withPivot('user_id');
}

// 自定义作用域：统计指定reaction的数量并排序
public function scopeWithReactionCount($query, $reactionId)
{
    return $query->withCount([
        'reactions' => function ($q) use ($reactionId) {
            $q->where('reaction_id', $reactionId);
        }
    ])->orderBy('reactions_count', 'desc');
}

控制器中直接调用：

// Controller
$reactionId = 1;
$posts = Post::withReactionCount($reactionId)->get();

返回的每个Post实例会自动携带reactions_count字段，代表对应指定reaction_id的统计数，且已按该字段降序排序。

注意事项 #

• 所有方法都使用了参数绑定（或Eloquent自动处理），避免直接拼接$reactionId到SQL语句中，防止SQL注入风险。
• 上述方法都会保留没有对应reaction的帖子，统计数为0。

内容的提问来源于stack exchange，提问作者Dipen Chand