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
)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…