0

I have a MariaDB query that runs fine in the database query.

SELECT student_id, last_name, first_name , Count(id) as courses FROM registrations
WHERE student_id NOT IN (SELECT student_id FROM exclude_from_tracking_list)
GROUP BY student_id
HAVING COUNT(*) > 3
ORDER BY last_name

But can not get it to run in laravel 8.

DB::statement('with query')

DB::table('registrations') 
        ->selectRaw('student_id, last_name, first_name , Count(id) as courses')
        ->where(DB::raw('student_id NOT IN (SELECT student_id FROM exclude_from_tracking_list'))    
        ->groupBy('student_id')
        ->havingRaw('COUNT(*) > 3')
        ->orderBy('last_name')
        ->get();

What am I missing. I've search and tried many other options.

But I keep getting this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'is null group by student_id having COUNT() > 3 order by last_name asc' at line 1 (SQL: select student_id, last_name, first_name , Count(id) as courses from registrations where student_id NOT IN (SELECT student_id FROM exclude_from_tracking_list is null group by student_id having COUNT() > 3 order by last_name asc)

Any help would be appreciated.

miken32
  • 42,008
  • 16
  • 111
  • 154
Jimmy
  • 3
  • 2
  • Maybe next time you will format code correctly - check guidelines.. You can do something like this: `DB::table('registrations') ->selectRaw('student_id, last_name, first_name, Count(id) as courses') ->whereNotIn('student_id', function($query) { $query->select('student_id')->from('exclude_from_tracking_list'); }) ->groupBy('student_id') ->havingRaw('COUNT(*) > 3') ->orderBy('last_name') ->get();` – Andreiaşi Marian Mar 28 '23 at 19:40
  • Just as a side note; I don't know what the exact limit is for laravel but using `WHERE IN/NOT IN` usually has a max number of records (I think in Oracle it's around 1000 items, I see a SO post that says laravel has 1500) so depending on your needs, it may be better to change it to a `WHERE NOT EXISTS` or even a subquery to avoid the limits. source: https://stackoverflow.com/a/73170298/8678978 – chrisbyte Mar 28 '23 at 20:41

1 Answers1

0

Your error message includes "is null" but there's nothing like that in the code you've shown. But, in general you should always aim to keep use of raw statements to a minimum. Something like this should be preferred, and may resolve the error you're seeing.

$excluded = DB::table('exclude_from_tracking_list')->select('student_id');

$result = DB::table('registrations')
    ->select('student_id', 'last_name', 'first_name')
    ->selectRaw('count(id) as courses')
    ->whereNotIn('student_id', $excluded)
    ->groupBy('student_id')
    ->having('courses', '>', 3)
    ->orderBy('last_name')
    ->get();

See Laravel documentation on available query builder methods. For troubleshooting, replace get() with toSql() and inspect the output.

miken32
  • 42,008
  • 16
  • 111
  • 154