From my various searches, I suspect he below issue stems from the fact that I am using GROUP BY. However, none of the examples I cam across were as simple as mine and their solutions ended up being something I couldnt grasp. I hope you can help me work out what is going wrong here?
I have this query with several joins which I am using in my app. When I run the query in a mysql client (SQLYog) remotely connecting to the homestead VM - I get the correct results. When I SSH into the VM and run MySQL and run the query, I get the correct results. When the query is run in Laravel/the app however, the results are different and I dont understand why!
Query:
SELECT monthly_contribution, COUNT(*) AS contributors FROM students
INNER JOIN student_bursaries ON students.id=student_bursaries.student_id
INNER JOIN student_bursary_statuses ON student_bursaries.id=student_bursary_statuses.student_bursary_id
LEFT JOIN student_medical_aids ON students.`id`=student_medical_aids.`student_id`
INNER JOIN bursary_administrator_student ON students.`id`=bursary_administrator_student.`student_id`
WHERE student_bursary_statuses.`status` IN (1,4,6,7) AND
bursary_administrator_student.`bursary_administrator_id`=1
GROUP BY monthly_contribution
MySQL results:
+----------------------+--------------+
| monthly_contribution | contributors |
+----------------------+--------------+
| 50.00 | 151 |
| NULL | 7 |
| 150.00 | 4 |
| 100.00 | 1 |
| 250.00 | 3 |
+----------------------+--------------+
Laravel results:
+----------------------+--------------+
| monthly_contribution | contributors |
+----------------------+--------------+
| 50.00 | 141 |
| NULL | 2 |
| 150.00 | 4 |
| 100.00 | 1 |
| 250.00 | 2 |
+----------------------+--------------+
I tried COUNT(monthly_contribution) but that returns the count of NULL value as 0 - and I need the count there. Also, it doesnt fix the miscounts of the others anyway, so its not that.
I did use Log::debug(DB::getQueryLog()); and it did output the same query so doesnt appear to be an issue there either:
Laravel Code:
$medical_aids = DB::select('SELECT monthly_contribution, COUNT(*) AS contributors FROM students
INNER JOIN student_bursaries ON students.id=student_bursaries.student_id
INNER JOIN student_bursary_statuses ON student_bursaries.id=student_bursary_statuses.student_bursary_id
LEFT JOIN student_medical_aids ON students.`id`=student_medical_aids.`student_id`
INNER JOIN bursary_administrator_student ON students.`id`=bursary_administrator_student.`student_id`
WHERE student_bursary_statuses.`status` IN (:status) AND
bursary_administrator_student.`bursary_administrator_id`=:bursary_administrator_id
GROUP BY monthly_contribution',
[
'status' => '1,4,6,7',
'bursary_administrator_id' => \Auth::user()->userable_id
]);
Laravel Log File:
[2021-07-16 10:44:34] local.DEBUG: array (
0 =>
array (
'query' => 'SELECT monthly_contribution, COUNT(*) AS contributors FROM students
INNER JOIN student_bursaries ON students.id=student_bursaries.student_id
INNER JOIN student_bursary_statuses ON student_bursaries.id=student_bursary_statuses.student_bursary_id
LEFT JOIN student_medical_aids ON students.`id`=student_medical_aids.`student_id`
INNER JOIN bursary_administrator_student ON students.`id`=bursary_administrator_student.`student_id`
WHERE student_bursary_statuses.`status` IN (:status) AND
bursary_administrator_student.`bursary_administrator_id`=:bursary_administrator_id
GROUP BY monthly_contribution',
'bindings' =>
array (
'status' => '1,4,6,7',
'bursary_administrator_id' => 1,
),
'time' => 1.83,
),
Please advise if you need more data?
What I see in your query is that you are using binded parameters in IN()
function.
If you want to use that you have to bind each of them.
For example:
DB::select("SELECT * FROM table WHERE id IN (?,?,?,?)", [1,2,3,4]);
DB::select("SELECT * FROM table WHERE id IN (:status1,:status2,:status3,:status4)", [
'status1' => 1,
'status2' => 2,
'status3' => 3,
'status4' => 4,
]);
In your case you can use join
statement in your query.
DB::select("SELECT * FROM table WHERE id IN (". DB::connection()->getPdo()->quote(implode(',', [1,2,3,4])) . ")");
Or User Larave's ORM to build query.
Or Implement some other complex logic, Which I don't recomend.