mercredi 25 août 2021

how to get users group by roles in laravel query builder

I have three tables 1 users table, 2 roles table and 3 role_users table as pivot table. Each user may have multiple roles such as editor and publisher at the same time maybe

Tables Structure

  1. Users                  id name email
  2. Roles                  id name
  3. Role_users          id role_id user_id

first user has 2 different roles editor & publisher with my query it's repeating the user with new role every time. How can I group them as array of roles in result

Query

        $query = DB::table('users')
       ->select(
        'users.name As name',
        'users.email As email',
        'users.status As status',
        'roles.name As role'
        );

    $query->leftjoin('role_users', 'users.id', '=', 'role_users.user_id');
    $query->leftjoin('roles', 'role_users.role_id', '=', 'roles.id');
    $query->groupBy('roles.name');
    $result = $query->get();

    dd($result->toArray());

Result Required

    "name": "Billyshawn"
    "email": "billy@shawn.com"
    "role": ["editor","publisher"]


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire