Laravel SQL Result Different Than MySQL Query

Question

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?

each value in the IN must be its own parameter. If it's not then you end up with a query like WHERE student_bursary_statuses.status IN ('1,4,6,7') because the comma separated integers are treated as a string. You should make some effort to convert the query to a query builder query where you can use whereIn([array]) — apokryfos
– apokryfos, Commented Jul 16, 2021 at 11:35

Malkhazi Dartsmelidze · Accepted Answer · 2021-07-16 11:45:04Z

1

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.

answered Jul 16, 2021 at 11:45

Malkhazi Dartsmelidze

4,9924 gold badges20 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SupaMonkey Over a year ago

That did the trick - thank you! didnt know they had to be individually binded! Bonus points if you can point to a resource that goes into more detail as to why, etc.

Malkhazi Dartsmelidze Over a year ago

The simplest documentation: laravel.com/docs/8.x/queries#raw-expressions

Collectives™ on Stack Overflow

Laravel SQL Result Different Than MySQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related