How to combine Laravel Left Join and Group By for another Table

I am using Laravel and have two tables user and result.

      user                                    result
---------------------------          -------------------------------------
| `id`  | `name` |`active`|          | `id` |`user_id` |`status`|`identity`|
--------------------------            ------------------------------------
|   1   | Apia   | 1    |            |   1   |    1   |    2   |    1   |
|   2   | Tang   | 1    |            |   2   |    1   |    2   |    1   |
|   3   | Jemrom | 1    |            |   3   |    2   |    1   |    1   |
|   4   | Akwet  | 1    |            |   4   |    5   |    3   |    1   |
|   5   | Lamte  | 1    |            -------------------------------------
---------------------------

So the result should look like:

------------------------------
| `name` |`active` |`status`  |
------------------------------
| Apia   |    1    |`passed`  |
| Tang   |    1    |`passed`  |
| Akwet  |    1    |`awaiting`|
| Lamte  |    1    |`failed`  |
| Jerome  |    1    |`unavailable`  |
------------------------------

NB: For status in result table (1=awaiting, 2=passed,3=failed)

I want to select everything from user table using left join where active = 1 (ordered by names) and match with related user_id in result table where identity =1. The result table must be grouped by user_id. Where a user does not have data in result, it should display unavailable for status instead of null

How do I write this query in Laravel?

Thanks

fatenfalfoul

5 years ago

Level 1

//get users that have status $users1 = DB::table('users')->leftjoin('result', 'users.id', '=', 'result.user_id')->where('users.active', '=', '1')->where('result.identity', '=', '1')->select('users., 'result.status')->orderBy(users.name', asc'); //get the rest of users and union the rest ;) $users2 = DB::table('users')->whereNOT('id', 'status.user_id')->select('users.', , DB::raw('0 as status')])->union($users1)->get();

on the view blade just add if else to replace status with desired name for example if 0 ==> unavailable ;) Good luck :D

Please or to participate in this conversation.