Assume that I have Post model that stores any "post" created by users, and PostLog models that stores the changes log of a Post model. Obviously, the Post is having hasMany() relation to PostLog.

Now I want to define a has-one-of-many out of that relation. The PostLog has user_id which define the user who makes the changes. I want to retrieve the latest PostLog created by users with a specific role.

Regarding to the users and roles table, their relation is established as many-to-many, meaning that there's role_user that connects the two of them.

This is the best that I came up with, but I'm not getting the correct value (obviously):

class Post extends Model
{
    public function latestAdminPostLog()
    {
        return $this->hasOne(PostLog::class)->ofMany([
            'id' => 'max',
            'created_at' => 'max'
        ], function ($query) {
            $query->whereExists(function ($qry) {
                $qry->select(DB::raw(1))
                    ->from('users')
                    ->join('role_user', 'users.id', '=', 'role_user.user_id')
                    ->join('roles', 'role_user.role_id', '=', 'roles.id')
                    ->whereColumn('users.id', 'post_logs.user_id')
                    ->whereIn('roles.name', ['admin-x', 'admin-y']);
            });
        });
    }
}

Your help is much appeciated. Thanks!

Note: I'm using santigarcor/laratrust package for the user-role management.

CodePudding user response：

just do

public function latestAdminPostLog(){
     return $this->hasOne(PostLog::class)->whereHas('user', fn (Builder $query) => $query->whereRoleIs(['admin-x', 'admin-y']))->latest();
}

this returns latest PostLog that was created by a user that has admin-x or admin-y role

have a nice day