Laravel MongoDB多对多关联获取中间表sequence字段更优方案求解

需求背景 #

现有Post模型与Categories模型为多对多关联关系，中间关联表为PostCategory，表中存储post_id、category_id以及文章对应的排序字段sequence。需要在查询Post模型基础属性（title、description等）的同时，获取对应关联下的sequence值。

当前实现代码如下：

$posts = Post::where([
    'is_active' => 1,
    'is_deleted' => 0,
    'is_published' => 1,
    'status' => 'publish'
])->whereHas('category', function ($query) use ($params) {
    return $query->where([
        'category_id' => $params['categoryId'],
    ]);
})->with([
    'category' => function ($query) use ($params) {
        return $query->where([
            'category_id' => $params['categoryId'],
        ]);
    }
])
    ->orderBy('live_date', 'DESC')
    ->orderBy('publish_time', 'DESC')
    ->get()
    ->toArray();
    
foreach ($posts as &$post) {
    $post['sequence'] = $post['category']['sequence'];
}

上述代码可以得到预期结果，但存在明显冗余：需要在whereHas和with方法中重复编写相同的筛选闭包，且查询完成后需要遍历整个集合手动将sequence字段提到Post数组顶层，需要更优的实现方案。

现有模型定义 #

Post.php #

namespace App\Models\Mongo;


/**
 * @mixin \Illuminate\Database\Eloquent\Builder
 * @mixin \Jenssegers\Mongodb\Query\Builder
 */
class POST extends \Jenssegers\Mongodb\Eloquent\Model
{
    /** @var string Mongo Connection Name */
    //protected $connection = 'mongodb';

    /** @var string Mongo Collection Name */
    protected $collection = 'posts';

    /** @var bool Enable/Disable Timestamp */
    public $timestamps = true;

    /** @var string Date format */
    protected $dateFormat = 'Y-m-d H:i:s';

    /** @var array */
    protected $dates = ['created_at', 'updated_at', 'live_date', 'expire_date'];

    /**
     * // 我知道这个关联不正确，应该用belongsToMany或者hasMany
     * // 但目前我需要查询归属单个分类ID的文章，所以暂时用hasOne关联
     * @return \Jenssegers\Mongodb\Relations\HasOne
     */
    public function category()
    {
        return $this->hasOne(
            PostCategory::class,
            'post_id',
            '_id'
        );
    }
}

PostCategory.php #

namespace App\Models\Mongo;


/**
 * @mixin \Illuminate\Database\Eloquent\Builder
 * @mixin \Jenssegers\Mongodb\Query\Builder
 */
class PostCategory extends \Jenssegers\Mongodb\Eloquent\Model
{
    /** @var string Mongo Connection Name */
    //protected $connection = 'mongodb';

    /** @var string Mongo Collection Name */
    protected $collection = 'post_category';

    /**
     * @return \Jenssegers\Mongodb\Relations\HasMany
     */
    public function post()
    {
        return $this->hasMany(Post::class, '_id', 'post_id');
    }
}

已尝试的修改 #

尝试将Post中的关联改为belongsToMany，但关联无法正常生效，修改代码如下：

return $this->belongsToMany(
    Category::class,
    'post_category',
    'post_id',
    'category_id',
    '_id', // post primary key
    '_id', // category primary key
)->withPivot('sequence');

分两种场景实现：

场景1：暂时保留现有hasOne关联，快速优化冗余代码 #

直接用Laravel内置的withWhereHas方法替代分开的whereHas+with调用，自动同时完成关联筛选和预加载，无需重复编写闭包；再用集合的map方法替代引用遍历处理字段，代码更安全：

$posts = Post::where([
        'is_active' => 1,
        'is_deleted' => 0,
        'is_published' => 1,
        'status' => 'publish'
    ])
    ->withWhereHas('category', fn ($query) => $query->where('category_id', $params['categoryId']))
    ->orderBy('live_date', 'DESC')
    ->orderBy('publish_time', 'DESC')
    ->get()
    ->map(fn($post) => [...$post->toArray(), 'sequence' => $post->category->sequence])
    ->toArray();

场景2：修复belongsToMany关联，彻底符合多对多设计规范 #

之前关联不生效大概率是Mongo的主键类型匹配问题，首先确保Category模型已正确定义，再修正Post模型的关联：

// Post.php中新增关联，替换原有临时的category关联
public function categories()
{
    return $this->belongsToMany(
        Category::class,
        'post_category',
        'post_id',
        'category_id',
        '_id',
        '_id',
    )->withPivot('sequence');
}

查询时注意，如果$params['categoryId']是字符串形式的MongoID，需要先转成ObjectId类型再匹配，避免关联查询失效：

use MongoDB\BSON\ObjectId;

$categoryId = new ObjectId($params['categoryId']);
$posts = Post::where([
        'is_active' => 1,
        'is_deleted' => 0,
        'is_published' => 1,
        'status' => 'publish'
    ])
    ->withWhereHas('categories', fn ($query) => $query->where('_id', $categoryId))
    ->orderBy('live_date', 'DESC')
    ->orderBy('publish_time', 'DESC')
    ->get()
    ->map(function($post) {
        $data = $post->toArray();
        $data['sequence'] = $post->categories[0]->pivot->sequence;
        // 可按需删除不需要的关联数据
        unset($data['categories']);
        return $data;
    })
    ->toArray();

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