Transform Raw Union Query into Eloquent Relationship

Question

i've a question about Laravel Relationships.

I've a raw union query and i want to translate into a eloquent relationship.

First of all... i have 4 tables involved:

roles

id|name

permissions

id|name|code|description

permission_role

role_id|permission_id

users

id|...........|role_id

permission_user

user_id|permission_id

Inside my User model, i've this method:

/**
 * @TODO: Transform this into a eloquent relationship
 * 
 * @return Collection
 */
public function permissions()
{
    $query = sprintf('
        (
            SELECT permissions.*
            FROM permissions
            INNER JOIN permission_role ON permission_role.permission_id = permissions.id
            WHERE permission_role.role_id = %s
        ) UNION
        (
            SELECT permissions.*
            FROM permissions
            INNER JOIN permission_user ON permission_user.permission_id = permissions.id
            WHERE permission_user.user_id = %s
        )', $this->role_id, $this->id);

    return Permission::hydrate(DB::select($query));
}

The point is, i want to fetch all permissions by the role that the user is associated, and the separated permissions associated to the user.

Can i transform this in some eloquent relationship like hasMany, belongsToMany, etc... ?

i'm builting a full API, so i'm using passport scopes to generate token with permissions(as scopes), i've already finished everything , i just want to improve this code — Allan Maia Fernandes

Praveen Gopagani Praveen Gopagani · Accepted Answer · 2018-12-04T19:12:51

The "merge" function in Laravel collection might be able to help you. The big differnt is that I close off the query with ->get() in advance, and I use merge() instead of union()

 // In Controller 
 public function GetUsersWithPermission()
 {
      $permissionByRole = User::with('permission_role.permission')->get();
      $permissionByUser = User::with('permission_user.permission')->get();

      $result = $permissionByRole->merge($permissionByUser);
 }


// User Model : get PermissionRole By User 
public function permission_role() {
    return $this->hasOne('App/Model/permission_role', 'role_id', 'role_id'); }

public function permission_user() {
    return $this->hasOne('App/Model/permission_user', 'user_id', 'id'); }



// permission_role Model : get Permissions By Role 
public function permission(){
    return $this->hasMany('App/Model/Permissions', 'id', 'permission_id');    }

// permission_user Model : get Permissions By User 
public function permission(){
    return $this->hasMany('App/Model/Permissions', 'id', 'permission_id');    }

Note: I don't have your data so I can't proof it work, but it least it work on my data so should worth your try. and it return all data like: all user details, and permissions so you can use select() function to get Specific Columns.

Transform Raw Union Query into Eloquent Relationship

1 Answers