Level 104
"May 11 8 PM" !== '2020-05-13 20:00'
May 13, 2021
5
Level 8
laravel subquery cant filter created_at date
Hello I have subquery where it will get the total_entries and total_withdrawal of the user, but I'm having a problem with getting the date which is greater than or equal to May 11 8 PM on the entries and withdrawals table, it still getting the date that is below May 11 8 PM.
This is my current code
$referrals = User::find($user->id)->descendants()->whereDepth('<', 10)
->select('first_name')
->addSelect(['total_entries' => Entries::selectRaw('sum(amount) as entries')
->whereColumn('user_id','laravel_cte.id')
->where('entries.created_at', '>=', '2021-05-11 20:00')
])
->addSelect(['total_withdrawal' => Withdrawal::selectRaw('sum(amount) as withdrawal')
->whereColumn('user_id','laravel_cte.id')
->where('withdrawals.created_at', '>=', '2021-05-11 20:00')
])->where('active',1)->get();
The query returns this
0 => App\User {#1532 ▼
#original: array:3 [▼
"first_name" => "John"
"total_entries" => "6000"
"total_withdrawal" => "1000"
]
1 => App\User {#1533 ▼
#original: array:3 [▼
"first_name" => "Regina"
"total_entries" => "8000"
"total_withdrawal" => "3000"
]
Level 8
@tykus sorry, I've updated my post. Thank you
Level 104
Ok, so what is wrong about the result?
Level 8
May bad, I was typing 2020 instead of 2021, I posted my final query below.
Level 8
My bad. I got it working now.
$minimum_date = Carbon::parse('2021-05-10 22:00');
$date = Carbon::parse($minimum_date )->format('Y-m-d H:i:s');
$referrals = User::find($user->id)->descendants()->whereDepth('<', 10)
->addSelect(['total_entries' => Entries::selectRaw('coalesce(sum(amount),0) as entries')
->whereColumn('user_id','laravel_cte.id')
->whereDate('entries.created_at', '>=', $date)
])
->addSelect(['total_withdrawal' => Reinvest::selectRaw('coalesce(sum(amount),0) as withdrawal')
->whereColumn('user_id','laravel_cte.id')
->whereDate('withdrawals.created_at', '>=', $date)
])->where('active',1)->get();
1 like
Please or to participate in this conversation.