Laravel distinct on join one to many

Question

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?

i assume `Joe Bloggs` has id `1` only. then u can do, `$users->unique('id')`; — arun, Jun 04 '18 at 09:28
sr did you check my answer?it works you dont have to ask 10 questions — , Jun 05 '18 at 04:34
It didnt work for me in my case @Eminem, i'm not sure what the problem could be. The task ended up changing so I no longer need to achieve this but thank you for your help. — S_R, Jun 05 '18 at 08:11
it's not a matter of doing your task,for us too it takes effort and time to answer a question, you have asked multiple questions without awarding anyone,very rude — , Jun 05 '18 at 08:41
@Eminem you're right I apologise. Thank you for taking the effort to try and help me. — S_R, Jun 05 '18 at 11:55

score 1 · Answer 1 · answered Jun 04 '18 at 10:14

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

score 0 · Answer 2 · 2018-06-04T18:57:46.213

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!

Laravel distinct on join one to many

2 Answers2