Laravel controller query-CodePudding

I have a laravel project with Laravel Framework 5.8.38 I try to convert mysql query:

SELECT b.name as business_location_name , u.first_name as cashier_first_name , u.last_name as cashier_last_name , cr.location_id , SUM(CASE WHEN tp.is_return = '0' AND tp.method = 'cash' THEN tp.amount ELSE 0 END) totalCash, SUM(CASE WHEN tp.is_return = '1' AND tp.method = 'cash' THEN tp.amount ELSE 0 END) totalReturn , SUM(CASE WHEN tp.is_return = '0' AND tp.method = 'card' THEN tp.amount ELSE 0 END) totalCard , cr.created_at , cr.closed_at
FROM cash_registers as cr
LEFT JOIN transaction_payments as tp ON cr.user_id = tp.created_by
LEFT JOIN users as u ON u.id = cr.user_id
LEFT JOIN business_locations as b ON b.id = cr.location_id
WHERE (tp.paid_on BETWEEN cr.created_at AND cr.closed_at) AND cr.status = "close" AND cr.created_at LIKE "2022-06-01%"
GROUP BY cr.location_id , cr.user_id
ORDER BY cr.location_id ASC, cr.user_id ASC

To lavravel query

$startDate = "2022-06-01";
   $results3 = DB::table('cash_registers as cr')
           ->select(DB::raw('b.name as business_location_name , u.first_name as cashier_first_name , u.last_name as cashier_last_name , cr.location_id , SUM(CASE WHEN tp.is_return = "0" AND tp.method = "cash" THEN tp.amount ELSE 0 END) totalCash, SUM(CASE WHEN tp.is_return = "1" AND tp.method = "cash" THEN tp.amount ELSE 0 END) totalReturn , SUM(CASE WHEN tp.is_return = "0" AND tp.method = "card" THEN tp.amount ELSE 0 END) totalCard , cr.created_at , cr.closed_at'))
           ->leftjoin('transaction_payments as tp','tp.created_by', '=', 'cr.user_id')
           ->leftjoin('users as u','u.id', '=', 'cr.user_id')
           ->leftjoin('business_locations as b','b.id', '=', 'cr.location_id')
           ->whereBetween('tp.paid_on',['cr.created_at', 'cr.closed_at'])
           ->where('cr.status', '=' , 'close')
           ->where("cr.created_at", "like", $startDate.'%')
           ->groupBy('cr.location_id')
           ->groupBy('cr.user_id')
           ->orderBy('cr.location_id', 'asc')
           ->orderBy('cr.user_id', 'asc')
           ->get();

But when I run the query on phpmyadmin ,it returns correct result , but when fire the laravel query , it returns empty array []

What's the problem ?

Thanks in advance

CodePudding user response：

You used the whereBetween function incorrectly:

->whereBetween('tp.paid_on',['cr.created_at', 'cr.closed_at'])

sql: `tp`.`paid_on` between 'cr.created_at' and 'cr.closed_at'

use this instead:

->whereRaw('tp.paid_on between cr.created_at and cr.closed_at')

sql: `tp`.`paid_on` between cr.created_at and cr.closed_at

but what about DB::select?

use Illuminate\Support\Facades\DB;

$startDate = "2022-06-01";

$results = DB::select(<<<SQL
SELECT b.name as business_location_name, u.first_name as cashier_first_name , u.last_name as cashier_last_name , cr.location_id, SUM(CASE WHEN tp.is_return = '0' AND tp.method = 'cash' THEN tp.amount ELSE 0 END) totalCash, SUM(CASE WHEN tp.is_return = '1' AND tp.method = 'cash' THEN tp.amount ELSE 0 END) totalReturn , SUM(CASE WHEN tp.is_return = '0' AND tp.method = 'card' THEN tp.amount ELSE 0 END) totalCard, cr.created_at, cr.closed_at
FROM `cash_registers` as cr
LEFT JOIN `transaction_payments` as tp ON cr.user_id = tp.created_by
LEFT JOIN `users` as u ON u.id = cr.user_id
LEFT JOIN `business_locations` as b ON b.id = cr.location_id
WHERE (tp.paid_on BETWEEN cr.created_at AND cr.closed_at) AND cr.status = "close" AND cr.created_at LIKE ?
GROUP BY cr.location_id, cr.user_id
ORDER BY cr.location_id, cr.user_id
SQL, [$startDate . '%']);