Better solution to get column from Relationship with Main Model columns

Need a better solution

There is a Post which belongs to multiple Categories and both having Many-to-Many relationship in between them. The intermediate table for many-to-many relationship is PostCategory. PostCategory contains post_id, category_id and sequence of the post. I want to get this sequence with the Post model attributes (title, description, ...).

To get this, am doing like this

$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'];
}

Am getting the expected result but as you can see, first I've to use the closure twice and then have to iterate through entire collection to set sequence at the top-level but as I mentioned, I need a better solution to this (If any)

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'];

    /**
     * // I know this relation is not correct, it must either belongsToMany or hasMany
     * // but as of now, I've to fetch the posts belonging to a single category id
     * // so using hasOne relation
     * @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');
    }
}

Changes

change relation to belongsToMany in Post

Relation is not working

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

You could use a many-to-many relationship instead and access sequence as pivot column.

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'];

    /**
     * // I know this relation is not correct, it must either belongsToMany or hasMany
     * // but as of now, I've to fetch the posts belonging to a single category id
     * // so using hasOne relation
     * @return \Jenssegers\Mongodb\Relations\HasOne
     */
    public function category()
    {
        return $this->belongsToMany(
            Category::class
        )->withPivot('sequence');
    }
}

You probably have to add one or more optional parameters to belongsToMany() to make it work. But since you know your data structure better than I do, I bet, you can figure that out faster than I can.