Better solution to get column from Relationship with Main Model columns

Question

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

Whether I store the closure in a variable or not, both ways closure will be used twice and using closure twice is not a problem, problem is I've to iterate through entire collection just for that `sequence` — Haridarshan, May 21 '21 at 12:49
@shaedrich after changing relationship to `belongsToMany` its not working at all. I've added the changed relationship in the question, If you can check that will be great. — Haridarshan, May 21 '21 at 13:21
What do you mean by "it's not working at all"? Did you get any error message? — shaedrich, May 21 '21 at 13:25
Nope, am getting empty collection after changing the relation — Haridarshan, May 21 '21 at 13:26
Hard to say without seing the actual sql statement. Honestly, Laravel drove me crazy with its relationship syntax a dozen times :D Could you `$sql = $query->toSql(); dd($sql);`? — shaedrich, May 21 '21 at 13:30
Who says you cannot use `whereHas` there? Do you want to filter the pivto table? — shaedrich, May 21 '21 at 13:30
1. `toSql` doesn't provide the relationship queries, still here is the query `select * from "posts" where ("is_active" = ? and "is_deleted" = ? and "is_published" = ? and "status" = ?) and "_id" in (?, ?) order by "" , ""` 2. Yes, we can't use any `belongsTo` relation in `whereHas` method 3. Just a note, am using Eloquent outside laravel with Jenssengers Mongodb and all these models are mongo collection models — Haridarshan, May 21 '21 at 13:41
I'm afraid, I am at my wit's end. I'm confident, we're heading in the right direction but without fiddling around with that myself, I bet, I couldn't see what must be changed in my answer to make it fit with your use case. — shaedrich, May 21 '21 at 13:47

score 1 · Accepted Answer · answered May 21 '21 at 12:51

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.

Better solution to get column from Relationship with Main Model columns

1 Answers1