Laravel父子消息按最新时间排序的Eloquent查询优化需求

我有一个消息列表，消息支持回复（父子关联结构），列表仅展示父消息。需要实现：将自身或其子消息拥有最新时间戳的父消息排在列表最顶部。

当前我的Eloquent查询如下：

Message::withCount(['childMessages as latest_child_message' => function($query) {
    $query->select(DB::raw('max(created_at)'));
}])
->orderByDesc('latest_child_message')
->orderByDesc('created_at')
->get();

两次orderBy无法合并排序逻辑，会导致父消息或子消息的排序优先级单一。且场景不允许在数据库查询完成后对集合进行排序。

补充说明：

• 示例中最新回复（子消息ee）对应的父消息bb应排在列表底部；
• 该查询需用于返回Builder的静态方法中：

public static function getEloquentQuery(): Builder {
    $query = parent::getEloquentQuery();
    return $query->doTheMagicHere();
}

• 现有可行的SQL查询但性能极差：

SELECT
    id,
    comment,
    (SELECT MAX(cc.id) FROM comments cc WHERE cc.commentable_id = c.id) AS child_id
FROM
    comments c
WHERE
    commentable_type NOT LIKE '%Comment%'
ORDER BY CASE WHEN child_id IS NULL
              THEN id
              ELSE child_id
              END DESC
    ;

优化方案：用GREATEST()合并排序字段 #

直接利用数据库的GREATEST()函数，取父消息自身created_at与子消息最大created_at的较大值作为排序依据，一次排序即可实现需求，同时解决性能问题。

Eloquent实现代码

public static function getEloquentQuery(): Builder {
    $query = parent::getEloquentQuery();
    
    return $query->addSelect([
        'latest_activity' => function ($subQuery) {
            $subQuery->select(DB::raw('MAX(created_at)'))
                ->from('messages as child_messages')
                ->whereColumn('child_messages.parent_id', 'messages.id');
        }
    ])
    ->orderByDesc(DB::raw('GREATEST(created_at, COALESCE(latest_activity, created_at))'));
}

代码说明

• addSelect新增latest_activity字段，通过关联子查询获取当前父消息下所有子消息的最新创建时间；
• COALESCE(latest_activity, created_at)处理无回复的父消息，此时直接使用父消息自身的创建时间；
• GREATEST()函数直接对比父消息自身时间和子消息最新时间，取较大值作为排序键，降序排列后自然实现「最新活跃的父消息排最前」的逻辑。

性能优化建议

• 给messages.parent_id和messages.created_at创建联合索引，大幅提升子查询的检索效率；
• 针对父消息的筛选条件（如commentable_type的过滤规则）添加对应索引，缩小查询范围。

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