Laravel中如何基于Has Through关联表按评分排序活动列表？

嘿，这个需求很典型，咱们结合你的表结构来一步步搞定～

首先得明确：一个活动可能对应多个预订，每个预订又可能关联一个评分，所以通常我们会用平均评分作为活动的核心排序依据（当然也支持按最新/最高评分排序，后面会补充）。

方案1：按平均评分排序（最常用） #

这个方案会计算每个活动的平均评分，并且保留所有活动（哪怕没有评分的活动也会显示出来）：

SELECT 
    a.id, 
    a.name, 
    -- 把无评分的活动的平均评分设为0，不想改NULL值的话直接用AVG(r.rating)即可
    COALESCE(AVG(r.rating), 0) AS average_rating
FROM activity a
-- 左连接预订表，确保没有预订记录的活动也能被保留
LEFT JOIN booking b ON a.id = b.activity_id
-- 左连接评分表，确保有预订但没评分的活动也能被保留
LEFT JOIN ratting r ON b.id = r.booking_id
-- 按活动维度分组，计算每个活动的平均评分
GROUP BY a.id, a.name
-- 按平均评分降序排序（高分在前）
ORDER BY average_rating DESC;

细节说明： #

• 用LEFT JOIN而非INNER JOIN：避免过滤掉没有预订/没有评分的活动
• 如果保留AVG(r.rating)的NULL值，默认排序逻辑里NULL会排在末尾（降序排序时）

方案2：按最新评分排序 #

如果你想按每个活动最新提交的评分排序，可以用窗口函数实现（确保每个活动只出现一次）：

WITH latest_activity_ratings AS (
    SELECT 
        a.id, 
        a.name, 
        r.rating,
        -- 给每个活动的评分按id（假设id递增代表最新）排序，生成序号
        ROW_NUMBER() OVER (
            PARTITION BY a.id 
            ORDER BY r.id DESC, r.rating DESC
        ) AS rn
    FROM activity a
    LEFT JOIN booking b ON a.id = b.activity_id
    LEFT JOIN ratting r ON b.id = r.booking_id
)
SELECT id, name, rating
FROM latest_activity_ratings
-- 只取每个活动的最新评分记录
WHERE rn = 1
ORDER BY rating DESC;

细节说明： #

• PARTITION BY a.id：按活动分组，给每个活动的评分单独排序
• 如果你的评分表有专门的时间字段（比如created_at），可以把ORDER BY r.id DESC换成ORDER BY r.created_at DESC，排序逻辑更准确

方案3：按最高评分排序 #

和方案2逻辑类似，只需要调整窗口函数的排序规则，取每个活动的最高评分：

WITH top_activity_ratings AS (
    SELECT 
        a.id, 
        a.name, 
        r.rating,
        -- 按评分从高到低排序，评分相同则取最新的那条
        ROW_NUMBER() OVER (
            PARTITION BY a.id 
            ORDER BY r.rating DESC, r.id DESC
        ) AS rn
    FROM activity a
    LEFT JOIN booking b ON a.id = b.activity_id
    LEFT JOIN ratting r ON b.id = r.booking_id
)
SELECT id, name, rating
FROM top_activity_ratings
WHERE rn = 1
ORDER BY rating DESC;

根据你的实际业务需求选对应的方案就好啦～

内容的提问来源于stack exchange，提问作者Bhavika Patel