CODEWITHSUNDEEP
phpmysqllaraveleloquentgreatest-n-per-group
S_R
S_R

Reputation: 1998

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 | [email protected]

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 | [email protected]   | 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 | [email protected]   | 1         | 'In'   | 2018-06-04 09:01:00
2  | Joe Bloggs | [email protected]   | 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?

Upvotes: 1

Views: 4099

Answers (2)

user9025311
user9025311

Reputation:

Since you are using Laravel lets make the solution a bit more understandable and easier: First Lets create the models for each table:

class User extends Model
{
    public $timestamps = false;

    protected $fillable = [
       'FIRSTNAME', 
       'EMAIL_ADDRESS'
    ];

    public function status(){
    return $this->hasMany(\App\Status::class);
}
}

Now lets create the Status Model:

class Status extends Model
{
    public $timestamps = false;

    protected $casts = [
        'USER_ID' => 'int',
    ];

    protected $fillable = [
        'USER_ID',
       'STATUS',
        'DATE'
    ];

    public function user()
    {
        return $this->belongsTo(\App\User::class);
    }
}

Now you can use Eloquents like this:

 $result=User::whereHas('Status', function($q){
    $q->where('date', $current_date);
})->distinct('id')->get();

Hope it Helps!

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

To get latest record for each user you can use a self join for status table

select u.*,s.*
from user u
join status s on u.id  = s.user_id
left join status s1 on s.user_id = s1.user_id 
and s.date < s1.date 
where s1.user_id is null

Using query builder you might rewrite it as 

DB::table('user as u')
  ->select('u.*', 's.*')
  ->join('status as s', 'u.id ', '=', 's.user_id')
  ->leftJoin('status as s1', function ($join) {
        $join->on('s.user_id', '=', 's1.user_id')
             ->whereRaw(DB::raw('s.date < s1.date'));
   })
  ->whereNull('s1.user_id')
  ->get();

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

Upvotes: 1

Related Questions