Laravel多对多关系高效查询：查找共享分类的相关文章

posts
    - id
    - name

categories
    - id
    - name

category_post (中间表)
    - post_id
    - category_id

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    /**
     * 一篇文章可以属于多个分类
     */
    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

    /**
     * 一个分类可以包含多篇文章
     */
    public function posts()
    {
        return $this->belongsToMany(Post::class);
    }
}

use App\Models\Post;
use Illuminate\Support\Facades\DB;

$targetPostId = 1;

// 步骤1：获取目标文章的所有分类ID
$post = Post::find($targetPostId);
if (!$post) {
    // 处理文章不存在的情况
    return collect();
}
$categoryIds = $post->categories()->pluck('id');

// 步骤2：根据分类ID获取所有相关文章的ID（包括目标文章自身）
$postIds = DB::table('category_post')
    ->whereIn('category_id', $categoryIds)
    ->pluck('post_id')
    ->unique();

// 步骤3：查询最终的文章列表
$relatedPosts = DB::table('posts')
    ->whereIn('id', $postIds)
    ->get();

// 如果需要排除目标文章自身，可以添加此条件
$relatedPosts = $relatedPosts->where('id', '!=', $targetPostId);

use App\Models\Post;

$targetPostId = 1;

$relatedPosts = Post::whereHas('categories.posts', function ($query) use ($targetPostId) {
    // 这里的 'categories' 是 Post 模型中的关联方法
    // 这里的 '.posts' 是 Category 模型中的关联方法
    // $query 作用于 Category 模型所关联的 Post 模型
    $query->where('id', $targetPostId);
})
// 排除目标文章自身
->where('posts.id', '!=', $targetPostId)
->get();

// $relatedPosts 现在包含了所有与ID为1的文章共享至少一个分类的其他文章

SELECT *
FROM `posts`
WHERE EXISTS (
    SELECT *
    FROM `categories`
    INNER JOIN `category_post` ON `categories`.`id` = `category_post`.`category_id`
    WHERE `posts`.`id` = `category_post`.`post_id`
    AND EXISTS (
        SELECT *
        FROM `posts` AS `laravel_reserved_0`
        INNER JOIN `category_post` ON `laravel_reserved_0`.`id` = `category_post`.`post_id`
        WHERE `categories`.`id` = `category_post`.`category_id`
        AND `laravel_reserved_0`.`id` = ?
    )
)
AND `posts`.`id` != ?