Get most recent row of each event type for each user eloquent laravel

I have two tables: email_log

+---------+---------+-----------------------+--------------------------+---------------------+
| id      | user_id | type                  | email_log_event_types_id | created_at          |
+---------+---------+-----------------------+--------------------------+---------------------+
| 5300526 |  321032 | monthly_summary       |                        3 | 2018-06-16 12:33:20 |
| 5300442 |  256973 | scheduled_maintenance |                        2 | 2018-06-16 12:29:21 |
| 5300436 |  256973 | scheduled_maintenance |                        1 | 2018-06-16 12:22:21 |
| 5299887 |  256973 | monthly_summary       |                        3 | 2018-06-16 12:03:12 |
| 5299107 |  374144 | oil_change            |                        2 | 2018-06-16 11:36:28 |
| 5299099 |  374144 | oil_change            |                        1 | 2018-06-16 11:29:34 |
| 5296386 |  393151 | account_created       |                        3 | 2018-06-15 22:31:51 |
| 5295854 |  406493 | receipt               |                        3 | 2018-06-15 21:38:54 |
| 5295829 |  251469 | receipt               |                        3 | 2018-06-15 21:37:29 |
| 5292325 |  310518 | receipt               |                        3 | 2018-06-15 16:48:23 |
| 5291579 |  261984 | monthly_summary       |                        3 | 2018-06-15 16:14:30 |
| 5291311 |  311499 | receipt               |                        3 | 2018-06-15 15:54:25 |
| 5291212 |  350642 | receipt               |                        3 | 2018-06-15 15:49:06 |
| 5291016 |  311499 | receipt               |                        3 | 2018-06-15 15:41:16 |
| 5290787 |  477967 | registration_complete |                        7 | 2018-06-15 15:38:43 |
| 5290755 |  251785 | receipt               |                        2 | 2018-06-15 15:38:22 |
| 5290741 |  252976 | receipt               |                        2 | 2018-06-15 15:38:10 |
| 5290736 |  477967 | registration_complete |                        1 | 2018-06-15 15:31:55 |
| 5290705 |  251785 | receipt               |                        1 | 2018-06-15 15:31:31 |
| 5290691 |  252976 | receipt               |                        1 | 2018-06-15 15:31:23 |
+---------+---------+-----------------------+--------------------------+---------------------+

and email_log_event_types

+----+------------+-------------+---------------------+---------------------+------------+
| id | name       | title       | created_at          | updated_at          | deleted_at |
+----+------------+-------------+---------------------+---------------------+------------+
|  1 | processed  | Processed   | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  2 | delivered  | Delivered   | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  3 | open       | Open        | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  4 | click      | Click       | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  5 | dropped    | Dropped     | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  6 | spamreport | Spam Report | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
|  7 | bounce     | Bounce      | 2018-02-01 20:24:22 | 2018-02-01 20:24:22 | NULL       |
+----+------------+-------------+---------------------+---------------------+------------+

I want the result like each user have most recent multiple types with most recent email_log_event_types. Each row wil be unique with user_id, type and email_log_event_types_id

I have tried with ->latest()-get() and

$query = DB::table('email_log AS el')
        ->join('email_log_event_types AS elet', 'el.email_log_event_types_id', '=', 'elet.id')
        ->whereNotNull('el.email_log_event_types_id')
        ->leftJoin('email_log as el1', function ($join) {
         $join->on('el.id', '=', 'el1.id');
         $join->on('el.created_at', '<', 'el1.created_at')
        ->whereNull('el1.id');
   });

and

$query = DB::table('email_log AS el')
         ->join('email_log_event_types AS elet', 'el.email_log_event_types_id', '=', 'elet.id')
         ->whereNotNull('el.email_log_event_types_id')
         ->whereIn('el.id', function($query){
          $query->select('id')->where('el.created_at', DB::raw("(select max('created_at') from email_log)"));
});

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3bhG1J6
via IFTTT