Laravel中如何将关联结果转为对象数组并实现过滤、排序与分页

我正在开发一个通过能力项对活动进行多维度评分的项目，涉及以下数据表：

• Activities表：id、name、isNewest（当前值恒为1）
• activity_competences表：关联Activities、framework_competences与master_competences的中间表，包含id、activity_id（外键）、framework_competence_id（外键）、master_competence_id（外键）
• framework_competences表：id、name
• master_competences表：id、name

我需要通过SQL Join查询获取所有活动及其对应的能力项，同时支持用户按关键词过滤、排序结果并实现分页。当前已编写的查询代码如下：

$paginateOffset = isset($request->paginateOffset) ? $request->paginateOffset : 0;
$currentSort = isset($request->currentSort) ? $request->currentSort : 'id';
$currentSortDir = isset($request->currentSortDir) ? $request->currentSortDir : 'desc';

$activities = Activity::where('isNewest', 1)
    ->leftJoin('activity_competences', 'activities.id', '=', 'activity_competences.activity_id')
    ->leftJoin('framework_competences', 'activity_competences.framework_competence_id', '=', 'framework_competences.id')
    ->leftJoin('master_competences', 'activity_competences.master_competence_id', '=', 'master_competences.id')
    ->where(function($query) use ($request){// 按用户输入的关键词过滤
        $query->where('activities.name', 'LIKE','%'.$request->keyword.'%')
            ->orwhere('framework_competences.name', 'LIKE','%'.$request->keyword.'%')
            ->orwhere('master_competences.name', 'LIKE','%'.$request->keyword.'%');
    })->orderBy($currentSort, $currentSortDir)->offset($paginateOffset)->limit($paginateAmmount)
    ->get(array('activities.*', 'framework_competences.name as framework_competences', 'master_competences.name as master_competences'));

当前返回结果格式如下：

[
    {
        "id": 1,
        "name": "activity1",
        "framework_competences": "name1",
        "master_competences": "name3",
        "isNewest": 1
    },
    {
        "id": 2,
        "name": "activity2",
        "framework_competences": "name2",
        "master_competences": "name4",
        "isNewest": 1
    }
]

但我期望的返回格式是每个活动对应的能力项以数组形式呈现，示例如下：

[
    {
        "id": 1,
        "name": "manjil",
        "framework_competences": ["name1", "name2"],
        "master_competences": ["name3", "name4"]
    }
]

要实现将每个活动的能力项聚合为数组的需求，可通过以下两种方式处理：

方法一：使用Eloquent关联（推荐） #

首先在Activity模型中定义关联关系：

// Activity.php
public function frameworkCompetences()
{
    return $this->belongsToMany(FrameworkCompetence::class, 'activity_competences');
}

public function masterCompetences()
{
    return $this->belongsToMany(MasterCompetence::class, 'activity_competences');
}

然后修改查询逻辑，先过滤符合条件的活动，再预加载关联并聚合能力项：

$paginateOffset = $request->paginateOffset ?? 0;
$currentSort = $request->currentSort ?? 'id';
$currentSortDir = $request->currentSortDir ?? 'desc';
$paginateAmmount = $request->paginateAmmount ?? 10;
$keyword = $request->keyword ?? '';

// 先筛选符合条件的活动ID，避免Join导致的重复行影响分页准确性
$activityIds = Activity::where('isNewest', 1)
    ->where(function($query) use ($keyword) {
        $query->where('name', 'LIKE', "%{$keyword}%")
            ->orWhereHas('frameworkCompetences', function($q) use ($keyword) {
                $q->where('name', 'LIKE', "%{$keyword}%");
            })
            ->orWhereHas('masterCompetences', function($q) use ($keyword) {
                $q->where('name', 'LIKE', "%{$keyword}%");
            });
    })
    ->orderBy($currentSort, $currentSortDir)
    ->offset($paginateOffset)
    ->limit($paginateAmmount)
    ->pluck('id');

// 获取活动并处理聚合结果
$activities = Activity::whereIn('id', $activityIds)
    ->with(['frameworkCompetences:id,name', 'masterCompetences:id,name'])
    ->get()
    ->map(function($activity) {
        return [
            'id' => $activity->id,
            'name' => $activity->name,
            'framework_competences' => $activity->frameworkCompetences->pluck('name')->toArray(),
            'master_competences' => $activity->masterCompetences->pluck('name')->toArray(),
            'isNewest' => $activity->isNewest
        ];
    });

方法二：使用SQL聚合函数（GROUP_CONCAT） #

直接在查询中用GROUP_CONCAT聚合能力项，再转换为数组：

$paginateOffset = $request->paginateOffset ?? 0;
$currentSort = $request->currentSort ?? 'id';
$currentSortDir = $request->currentSortDir ?? 'desc';
$paginateAmmount = $request->paginateAmmount ?? 10;
$keyword = $request->keyword ?? '';

$activities = Activity::where('isNewest', 1)
    ->leftJoin('activity_competences', 'activities.id', '=', 'activity_competences.activity_id')
    ->leftJoin('framework_competences', 'activity_competences.framework_competence_id', '=', 'framework_competences.id')
    ->leftJoin('master_competences', 'activity_competences.master_competence_id', '=', 'master_competences.id')
    ->where(function($query) use ($keyword) {
        $query->where('activities.name', 'LIKE', "%{$keyword}%")
            ->orWhere('framework_competences.name', 'LIKE', "%{$keyword}%")
            ->orWhere('master_competences.name', 'LIKE', "%{$keyword}%");
    })
    ->selectRaw('activities.*, 
        GROUP_CONCAT(DISTINCT framework_competences.name) as framework_competences,
        GROUP_CONCAT(DISTINCT master_competences.name) as master_competences')
    ->groupBy('activities.id')
    ->orderBy($currentSort, $currentSortDir)
    ->offset($paginateOffset)
    ->limit($paginateAmmount)
    ->get()
    ->map(function($item) {
        // 将逗号分隔的字符串转为数组
        $item->framework_competences = $item->framework_competences ? explode(',', $item->framework_competences) : [];
        $item->master_competences = $item->master_competences ? explode(',', $item->master_competences) : [];
        return $item;
    });

注意事项 #

1. 方法一使用whereHas进行关联过滤，能避免Join产生的重复行问题，保证分页准确性；
2. 方法二中GROUP_CONCAT有默认长度限制，若能力项数量较多，需调整MySQL的group_concat_max_len配置；
3. 两种方法均保留了关键词过滤、排序和分页功能，符合需求。

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