My query is returning the wrong results and I think it is because the final parameter (minutes) is not being binded to the query. when I get the querylog everything seems fine but the wrong results are being returned. If I putthe minutes parameter directly into the query it returns the right results as expected but this value needs to be a variable.
To explain the query it is counting all records until the total minutes hit a set number, in my example im using 500.
This query works and returns results as expected:
DB::select('SELECT NULL AS session_total_charges, NULL AS call_date, NULL AS inbound_duration, NULL AS total
FROM dual
WHERE @total := 0
UNION ALL
SELECT session_total_charges, call_date, inbound_duration, @total := @total + inbound_duration AS total
FROM (SELECT * FROM records ORDER BY call_date) C where calling_user =:user and call_date LIKE :date AND outbound_zone_id IN ("UKX","UKM","UKLR","UKNR") and @total < 500', ["user"=>$user, "date"=>$date]);
This query does not work and only returns one row
DB::select('SELECT NULL AS session_total_charges, NULL AS call_date, NULL AS inbound_duration, NULL AS total
FROM dual
WHERE @total := 0
UNION ALL
SELECT session_total_charges, call_date, inbound_duration, @total := @total + inbound_duration AS total
FROM (SELECT * FROM records ORDER BY call_date) C where calling_user =:user and call_date LIKE :date AND outbound_zone_id IN ("UKX","UKM","UKLR","UKNR") and @total < :minutes', ["user"=>$user, "date"=>$date, "minutes"=>$minutes]);
[query] => SELECT NULL AS session_total_charges, NULL AS call_date, NULL AS inbound_duration, NULL AS total
FROM dual
WHERE @total := 0
UNION ALL
SELECT session_total_charges, call_date, inbound_duration, @total := @total + inbound_duration AS total
FROM (SELECT * FROM records ORDER BY call_date) C where calling_user =:user and call_date LIKE :date AND outbound_zone_id IN ("UKX","UKM","UKLR","UKNR") and @total < :minutes
[bindings] => Array
(
[user] => T-M000005251-009
[minutes] => 500
[date] => 2016-12-%%
)
//Result using bindings (only one row returned)
[0] => stdClass Object
(
[session_total_charges] => 0.014125
[call_date] => 2016-12-01 09:12:39
[inbound_duration] => 113
[total] => 113
)
//Result with values inserted directly into query(correct result returned)
[0] => stdClass Object
(
[session_total_charges] => 0.014125
[call_date] => 2016-12-01 09:12:39
[inbound_duration] => 113
[total] => 113
)
[1] => stdClass Object
(
[session_total_charges] => 0.04733333
[call_date] => 2016-12-01 09:18:16
[inbound_duration] => 142
[total] => 255
)
[2] => stdClass Object
(
[session_total_charges] => 0.03866667
[call_date] => 2016-12-01 09:22:21
[inbound_duration] => 116
[total] => 371
)
[3] => stdClass Object
(
[session_total_charges] => 0.012625
[call_date] => 2016-12-01 09:29:24
[inbound_duration] => 101
[total] => 472
)
[4] => stdClass Object
(
[session_total_charges] => 0.0505
[call_date] => 2016-12-01 12:03:16
[inbound_duration] => 404
[total] => 876
)
via ndev
