Laravel how to use model scope method inside join query

Question

i want to use scope method inside join subquery so is there any way I can do that in laravel?

Post Model

    public function scopePublished($query)
    {
        return $query->where('published',true);
    }

Now I want to join user table with post table but in join I want to use this scope method directly but its giving me error.

Users::join('posts',function($q){
    $q->on('posts.user_id','users.id');
    $q->published();

})->get();

So is there any way I can use scope directly inside join subquery ?

Answer 1

First, you need to add the relation between posts and users to the User model like so:

User Model

    public function posts()
    {
        return $this->hasMany(Post::class);
    }

and then your scope stays as it is, and your query if you wanna get users with their published posts:

 return User::with(['posts' => function ($query) {
                $query->published();
            }])
            ->get();

and if you want to get only users that have published posts:

return User::whereHas('posts', function ($query) {
                $query->published();
           })
           ->get();

Note that while with('posts') will include the related table's data in the returned collection, whereHas('posts') will not include the related table's data.

Hence sometimes you may need to call both together, I mean, only with('posts') will eager load relations (in this case posts).