Laravel : Select email column based on user role (Eloquent)-CodePudding

$users = User::select('users.id', 'users.username', 'users.active', 'users.parent_id', 'parent.username as parent_username')
->selectRaw('users.email as primary_email')
->selectRaw('user_profiles.secondary_email as secondary_email');

Currently I am fetching all users primary email & secondary email from users & user_profiles table as two separate columns for displaying user listing in front-end.

How can improve this query by just creating a single column email which contains primary email if user role has 'admin' otherwise it contains secondary_email for all other users other than admin

My Table Structure : Roles Table, User-Roles Table, Users Table (Using spatie laravel permissons)

CodePudding user response：

You need to modify below query as per your requirement:

$users = User::select("*",
                \DB::raw('(CASE 
                    WHEN users.status = "0" THEN "User" 
                    WHEN users.status = "1" THEN "Admin" 
                    ELSE "SuperAdmin" 
                    END) AS status_lable'))
            ->get();
dd($users);

this way you can return primary and secondary email based on user role.

CodePudding user response：

I am trying to select an email column based on user role (Eloquent)

My User model has a role_id and there are three roles 1,2,3. I have another table called emails which have columns emails.role_id, emails.email

Now I need to display the email in my user index view based on their role_id like if I logged in as admin I should get all user's emails or if I logged in as a parent I should get only the parent user's email

CodePudding user response：

Here is the modified version of query provided below

$users = User::select('users.id', 'users.username', 'users.active', 'users.parent_id', 'parent.username as parent_username')
            ->selectRaw("CASE WHEN GROUP_CONCAT(roles.name) = 'student' THEN user_profiles.secondary_email ELSE users.email END as email")