lundi 4 juin 2018

Laravel distinct on join one to many

I've found a couple of questions online similar to mine, but I can't seem to find a working answer.

I have 2 tables

USER

ID | FIRSTNAME  | EMAIL_ADDRESS
1  | Joe Bloggs | Joe@bloggs.com

STATUS

ID | USER_ID | STATUS | DATE
1  |  1      | 'In'   | 2018-06-04 09:01:00
2  |  1      | 'Out'  | 2018-06-04 09:00:00

I need to be able to Join the 2 tables together but only get the most recent status column by date, like this

ID | FIRSTNAME  | EMAIL_ADDRESS    | STATUS_ID | STATUS | DATE
1  | Joe Bloggs | Joe@bloggs.com   | 1         | 'In'   | 2018-06-04 09:01:00

I need to be able to run extra query builder arguments like where, because the user has the ability to pass in filters and search parameters if they require, so to be able to use the status table columns in my query builder, i'm doing a join like this

$users = Users::join('status', 'status.user_id', '=', 'user.id')->distinct('user.id');

Which then allows me to pass in any search parameters if I need them

         if(!empty($request->search)){
             $param = $request->search;
             $users = $users->where(function($query) use ($param){
                 $query->where('users.firstname', 'like', '%'.$param.'%')
                       ->orWhere('users.email_address', 'like', '%'.$param.'%');
             });
         }
         if(!empty($request->dateFrom)){
             if(!empty($request->dateTo)){
                 $users = $users->whereRaw('DATE(status.date) BETWEEN ? AND ?', [$request->dateFrom, $request->dateTo]);
             } else {
                 $users = $users->whereRaw('DATE(status.date) BETWEEN ? AND DATE(NOW())', [$request->dateFrom]);
             }
         }

Then run my get() at the end

    if($method == 'paginate'){
        $users = $users->paginate(10);
    } else {
        $users = $users->get();
    }

This returns the following

ID | FIRSTNAME  | EMAIL_ADDRESS    | STATUS_ID | STATUS | DATE
1  | Joe Bloggs | Joe@bloggs.com   | 1         | 'In'   | 2018-06-04 09:01:00
2  | Joe Bloggs | Joe@bloggs.com   | 1         | 'Out'  | 2018-06-04 09:00:00

I need to be able to use the foreign table columns as arguments in my Where functions, but I need to only return 1 row per user. How do I run a join, but only return 1 row for each of my users?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire