Level 55
$userOnLeave = DB::table('users')
->select('users.*', 'leaves.to')
->join('leaves', 'users.id', 'leaves.user_id')
->get()
->groupBy('to');
Sep 6, 2021
3
Level 1
Get users leaves as per date
users
id, name, email
1, john, [email protected]
2, martin, [email protected]
3, bob, [email protected]
leaves
id, user_id, from, to
1, 1, , 2021-09-01, 2021-09-05
2, 2, , 2021-09-05, 2021-09-05
3, 3, , 2021-09-02, 2021-09-05
//From this table i want data like key as date and value as user_id
'2021-09-01' => [
0 => 1
]
'2021-09-02' => [
0 => 1,
1 => 3
]
'2021-09-03' => [
0 => 1,
1 => 3
]
'2021-09-04' => [
0 => 1,
1 => 3
]
'2021-09-05' => [
0 => 1,
1 => 2,
2 => 3,
]
$userOnLeave = DB::table('users')
->join('leaves', function ($innerJoin) use ($query) {
$innerJoin->on('users.id', '=', 'leaves.user_id')
->whereRaw(?)
})->get();
Level 1
@silencebringer 2021-09-01 - 2021-09-03 and ignore Saturday and Sunday Then
'2021-09-01' => [
0 => 1
]
'2021-09-02' => [
0 => 1,
1 => 3
]
'2021-09-03' => [
0 => 1,
1 => 3
]
Also and date between filter on from and to.
Level 1
$userOnLeave = DB::table('users')
->select('users.*', 'leaves.to')
->join('leaves', 'users.id', 'leaves.user_id')
->where(function ($query) use ($startDate, $endDate) {
$where1 = [ ['leaves.from', '>=', $startDate], ['leaves.to', '<=', $endDate] ];
$where2 = [ ['leaves.from', '>=', $startDate], ['leaves.to', '<=', $endDate] ];
$where3 = [ ['leaves.from', '<=', $startDate], ['leaves.to', '>=', $endDate] ];
$orWhere1 = [ ['leaves.from', '<=', $startDate], ['leaves.to', '=', null] ];
$orWhere2 = [ ['leaves.from', '=', null], ['leaves.to', '>=', $endDate] ];
$orWhere3 = [ ['leaves.from', '=', null], ['leaves.to', '=', null] ];
$query->where($where1)->orWhere($where2)->orWhere($where3)
->orWhere($orWhere1)->orWhere($orWhere2)->orWhere($orWhere3);
})
->get()
->groupBy('to');
@silencebringer I added date filter bu how to ignore Saturday and Sunday
Please or to participate in this conversation.