Laravel关联字段为JSON数组时三表JOIN查询失效如何解决

原有JOIN语法只支持单值外键的等值匹配，当前场景下两个关联字段都是存储ID数组的JSON类型，加上原代码里写的关联字段名和实际表结构不符（原代码写的albums.track_id/tracks.singer_id，实际表结构为albums.tracks_id/tracks.singers_id），自然无法得到正确的关联结果。

MySQL 5.7及以上版本提供了JSON_CONTAINS函数，可以直接判断JSON数组中是否包含指定值，适配当前JSON存关联ID的场景，查询构造器实现代码如下：

Album::join('tracks', function ($join) {
        // 匹配albums的tracks_id数组中包含当前track主键的记录
        $join->whereRaw('JSON_CONTAINS(albums.tracks_id, CAST(tracks.id AS JSON), "$")');
    })
    ->join('singers', function ($join) {
        // 匹配tracks的singers_id数组中包含当前singer主键的记录
        $join->whereRaw('JSON_CONTAINS(tracks.singers_id, CAST(singers.id AS JSON), "$")');
    })
    // 必须指定查询字段，避免三表同名字段（id/name）互相覆盖
    ->select('albums.*', 'tracks.name as track_name', 'singers.name as singer_name')
    ->get();

• 代码中用CAST(主键id AS JSON)是为了把整数类型的主键转为JSON兼容格式，适配表中JSON数组存字符串类型ID的存储格式，避免类型不匹配导致关联失效。

注意：上述查询返回扁平化的笛卡尔积结果，比如1张专辑关联3首曲目、每首曲目关联2位歌手，最终会返回6条记录，每条对应该专辑下「专辑+单首曲目+单个歌手」的组合。

如果需要聚合为单条专辑记录，把关联的曲目、歌手合并展示，可以配合分组和聚合函数实现：

Album::join('tracks', function ($join) {
        $join->whereRaw('JSON_CONTAINS(albums.tracks_id, CAST(tracks.id AS JSON), "$")');
    })
    ->join('singers', function ($join) {
        $join->whereRaw('JSON_CONTAINS(tracks.singers_id, CAST(singers.id AS JSON), "$")');
    })
    ->select(
        'albums.id',
        'albums.name',
        \DB::raw('GROUP_CONCAT(DISTINCT tracks.name SEPARATOR "、") as track_names'),
        \DB::raw('GROUP_CONCAT(DISTINCT singers.name SEPARATOR "、") as singer_names')
    )
    ->groupBy('albums.id', 'albums.name')
    ->get();

不推荐用JSON字段存储多对多关联关系，只要业务允许调整表结构，优先遵循数据库三范式建多对多中间表：

• 专辑和曲目关联：建album_track中间表，字段为album_id、track_id，可额外加排序、加入时间等扩展字段
• 曲目和歌手关联：建singer_track中间表，字段为track_id、singer_id，可额外加歌手角色（主唱/作词/作曲）等扩展字段

用中间表实现多对多关联的优势很明显：关联查询可以走索引性能更高、支持更灵活的筛选条件、后续扩展关联属性更方便，也不会出现JSON字段更新时需要全量重写数组的问题。

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