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

Question

$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)

Answer 1

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.