Laravel中基于JSON类型tags字段查找相似视频的实现方案

我有一个videos表，其中tags是JSON字段，存储来自tags表的tag_id数组。加载某一视频时，需要通过这个字段查找相似视频：

• 目标视频video1的tags为[1,2,3,4]，要找出有至少一个共同标签的视频（如video2的[2,3,4,5]、video3的[3,4,5,6]、video4的[4,5,6,7]），但排除完全无交集的视频（如video5的[5,6,7,8]）
• 结果需要按与目标视频的共同标签数量从多到少排序，预期顺序为video2 > video3 > video4（分别有3、2、1个共同标签）
• 优先使用Laravel Eloquent方法，尽量避免原生SQL

以下是符合需求的Laravel实现代码：

// 获取目标视频
$targetVideo = Video::find(1);
$targetTags = $targetVideo->tags; // 假设tags字段已被自动转为数组

// 查询相似视频
$similarVideos = Video::query()
    // 排除当前视频本身
    ->where('id', '!=', $targetVideo->id)
    // 筛选至少有一个共同标签的视频
    ->where(function ($query) use ($targetTags) {
        foreach ($targetTags as $tag) {
            $query->orWhereJsonContains('tags', $tag);
        }
    })
    // 计算共同标签数量并作为字段返回
    ->selectRaw('*, JSON_LENGTH(JSON_INTERSECT(tags, ?)) as common_tags_count', [json_encode($targetTags)])
    // 按共同标签数量降序排序
    ->orderByDesc('common_tags_count')
    ->get();

1. 排除自身：通过where('id', '!=', $targetVideo->id)确保不会把当前视频加入结果
2. 筛选有交集的视频：用闭包循环目标标签，通过orWhereJsonContains匹配所有包含至少一个目标标签的视频，直接排除完全无交集的视频
3. 计算共同标签数量：利用MySQL的JSON_INTERSECT函数获取两个JSON数组的交集，再用JSON_LENGTH得到交集的元素个数，将其命名为common_tags_count
4. 排序：通过orderByDesc('common_tags_count')实现按共同标签数量从多到少排序，完全符合预期顺序

• MySQL版本要求：JSON_INTERSECT是MySQL 8.0.17及以上版本才支持的函数，如果你的MySQL版本较低，可以用以下替代方案（效率略低，但兼容性更好）：

  $similarVideos = Video::query()
      ->where('id', '!=', $targetVideo->id)
      ->where(function ($query) use ($targetTags) {
          foreach ($targetTags as $tag) {
              $query->orWhereJsonContains('tags', $tag);
          }
      })
      ->selectRaw('*, (' . implode(' + ', array_map(function ($tag) {
          return "JSON_CONTAINS(tags, '$tag')";
      }, $targetTags)) . ') as common_tags_count')
      ->orderByDesc('common_tags_count')
      ->get();
  

  这个方案通过逐个判断每个标签是否存在，将结果相加得到共同标签数量
• 索引优化：如果videos表数据量较大，建议给tags字段添加JSON索引，提升查询效率：

  CREATE INDEX idx_videos_tags ON videos((CAST(tags AS UNSIGNED ARRAY)));
  

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