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