Laravel中如何按指定service_code对关联服务的新闻对象数组排序

现有数据表结构 #

新闻表（News Table） #

服务表（Service Table） #

新闻服务关联表（News Service Table） #

已实现逻辑 #

已经完成新闻列表查询，并且为每条新闻匹配对应的服务编码、服务名称列表，关联效果如下：

现有代码 #

NewsTable模型类

class NewsTable extends Model
{
    protected $table      = "news";
    protected $primaryKey = "news_id";


    public function list(array $search = [])
    {
        return $this->select(
                            "{$this->table}.news_id",
                            "{$this->table}.news_title"
                            )
                    ->get();
    }
}

NewsServiceTable模型类

class NewsServiceTable extends Model
{
    protected $table      = "news_service";
    protected $primaryKey = "news_service_id";


    public function listById($id)
    {
        return $this->select("{$this->table}.service_code", "s.service_name")
                    ->leftJoin("service as s", "s.service_code", "=", "{$this->table}.service_code")
                    ->where("{$this->table}.news_id", $id)
                    ->get()
                    ->toArray();
    }
}

控制器类

class NewsController extends Controller
{
    protected $news;
    protected $newsService;


    public function __construct(NewsTable $news, NewsServiceTable $newsService)
    {
        $this->news    = $news;
        $this->newsService = $newsService;
    }

    public function listAction(Request $request)
    {
        $param = $request->filter;

        $data = $this->news->list();

        foreach ($data as $key => $item) {
            $service = $this->newsService->listById($item->news_id);
            $data[$key]["service"] = $service;
        }

        return $data;
    }
}

当前返回结果 #

[
    {
        "news_id": 1,
        "news_title": "Title 1",
        "service": [
            {
                "service_code": "apartment",
                "service_name": "Apartment"
            }
        ]
    },
    {
        "news_id": 2,
        "news_title": "Title 2",
        "service": [
            {
                "service_code": "home",
                "service_name": "Home"
            },
            {
                "service_code": "apartment",
                "service_name": "Apartment"
            }
        ]
    }
]

待实现功能 #

当控制器传入关键词（比如home）赋值给param变量后，对结果数组排序：service数组中包含对应关键词的新闻排在列表前方，其余排在后方，预期效果如下：

[
    {
        "news_id": 2,
        "news_title": "Title 2",
        "service": [
            {
                "service_code": "home",
                "service_name": "Home"
            },
            {
                "service_code": "apartment",
                "service_name": "Apartment"
            }
        ]
    },
    {
        "news_id": 1,
        "news_title": "Title 1",
        "service": [
            {
                "service_code": "apartment",
                "service_name": "Apartment"
            }
        ]
    }
]

提供两种实现方式，可根据业务数据量选择：

方案1：控制器层直接排序（适合数据量较小场景） #

无需修改原有模型代码，仅调整控制器listAction方法，在关联完服务数据后增加排序逻辑即可：

public function listAction(Request $request)
{
    $param = $request->filter;

    $data = $this->news->list();

    foreach ($data as $key => $item) {
        $service = $this->newsService->listById($item->news_id);
        $data[$key]["service"] = $service;
    }

    // 新增排序逻辑
    if (!empty($param)) {
        $data = $data->sort(function ($a, $b) use ($param) {
            $aHasKeyword = collect($a['service'])->contains('service_code', $param);
            $bHasKeyword = collect($b['service'])->contains('service_code', $param);
            // 包含关键词的排在前面
            if ($aHasKeyword && !$bHasKeyword) return -1;
            if (!$aHasKeyword && $bHasKeyword) return 1;
            return 0;
        })->values(); // 重置集合键名，保证返回结构为数组
    }

    return $data;
}

方案2：SQL层面查询排序（适合数据量较大场景，同时解决原有N+1查询性能问题） #

1. 首先在NewsTable模型中新增关联方法，用于预加载服务数据：

public function services()
{
    return $this->hasMany(NewsServiceTable::class, 'news_id', 'news_id')
        ->leftJoin('service as s', 's.service_code', '=', 'news_service.service_code')
        ->select('news_service.news_id', 'news_service.service_code', 's.service_name');
}

2. 调整NewsTable的list方法，支持关键词排序和预加载：

public function list(array $search = [])
{
    $keyword = $search['keyword'] ?? '';
    $query = $this->select("{$this->table}.news_id", "{$this->table}.news_title")
        ->with('services'); // 预加载关联服务，避免N+1查询
    
    // 存在关键词时新增排序字段
    if (!empty($keyword)) {
        $query->selectRaw("EXISTS(SELECT 1 FROM news_service ns WHERE ns.news_id = {$this->table}.news_id AND ns.service_code = ?) as has_keyword", [$keyword])
            ->orderBy('has_keyword', 'desc');
    }
    
    return $query->get();
}

3. 简化控制器逻辑，无需循环查询服务：

public function listAction(Request $request)
{
    $param = $request->filter;
    // 传入关键词到查询方法
    $data = $this->news->list(['keyword' => $param]);
    // 把关联的services重命名为service即可
    $data = $data->map(function ($item) {
        $item['service'] = $item->services;
        unset($item['services'], $item['has_keyword']);
        return $item;
    });
    return $data;
}

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