how to sum column in where clause and group by id in laravel

Question

I want to filter between low stock items and high stock item but I get an error this is my code

$this->authorize('isLowStock');
    $this->authorize('Read');
    $search = Request::get('search');
    $selected = Request::get('selected');
    $setting = Setting::first();
    return DB::table('medicines')
    ->join('stocks','medicines.id','stocks.medicine_id')
    ->leftjoin('classifications','medicines.classification_id','classifications.id')
            ->leftjoin('manufacturers','medicines.manufacturer_id','manufacturers.id')
            ->leftjoin('doctors','medicines.doctor_id','doctors.id')
            ->leftjoin('groups','medicines.group_id','groups.id')
            ->select([
                'medicines.*',
                'classifications.id as classification',
                'classifications.name as classificationName',
                'manufacturers.id as manufacturer',
                'manufacturers.name as manufacturerName',
                'doctors.id as doctor',
                'doctors.name as doctorName',
                'groups.id as group',
                'groups.name as groupName',
                DB::raw("SUM(stocks.quantity) as quantity")
            ])
   
    ->where(function($query) use ($search){
        if($search = Request::get('search'))
        {
            $query->where('medicines.barcode','LIKE',"$search%")
                ->orWhere('medicines.name','LIKE',"$search%");
        }
        
    })
    ->where(function($query) use ($selected,$setting){
        if($selected == 1)
        {
            $query->where(DB::raw('SUM(stocks.quantity'), '<=', 3);
        }
        if($selected == 2)
        {
            $query->where(DB::raw('SUM(stocks.quantity'), '>', 3);
        }
    })
    ->groupBy('medicines.id')
    ->paginate(20);

this the error I get

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by medicines.id' at line 1 (SQL: select count(*) as aggregate from medicines inner join stocks on medicines.id = stocks.medicine_id left join classifications on medicines.classification_id = classifications.id left join manufacturers on medicines.manufacturer_id = manufacturers.id left join doctors on medicines.doctor_id = doctors.id left join groups on medicines.group_id = groups.id where (SUM(stocks.quantity <= 3) group by medicines.id)

Answer 1

Add below modes to config/database.php

'mysql' => [
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],

    'modes' => [
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ENGINE_SUBSTITUTION',
    ],
],

As sugegsted here.

The above example enables both the group by and no zero date when querying.

Briefly with strict mode on you will have to:

1. Add all columns to group by.
2. Won't be able to use date's such as 0000-00-00 00:00:00
3. Fields like boolean will throw fatal if you pass something that isn't a boolean value, like 1, before it would convert it to true and save, with strict it fails.
4. You'll get an error if you divide a field by 0 (or another field that has 0 as value).