I'm using a function which is using alias name in "HAVING" clause and I'm getting an error,
"Unknown column 'orderStatusWithoutOpen' in 'having clause'".
Here is my code:
public static function getEquipmentPaginated($conditions = false, $id = false,$orderby='equipment_no',$order='asc',$filter=false)
{
if($orderby == 'fabricat_name')
{
$orderby = 'equipment_fabricat.fabricat_name';
}
if($orderby == 'model_name')
{
$orderby = 'equipment_model.model';
}
if($orderby == 'category_name')
{
$orderby = 'equipment_category.category_name';
}
if($orderby == 'customer_name')
{
$orderby = 'customer.name';
}
if($orderby == 'control_date')
{
$orderby = 'checklist.control_date';
}
if($orderby == 'status')
{
$orderby = 'checklist.status';
}
$equipment = Equipment::select('certificate.*');
$equipment->leftjoin('checklist','equipment_id','=','equipment.id');
$equipment->leftjoin('certificate','certificate.equipment_id','=','equipment.id');
$equipment->leftjoin('ordre','ordre.id','=','checklist.ordre_id');
$equipment->leftjoin('customer','equipment.customer_id','=','customer.id');
$equipment->leftjoin('equipment_fabricat','equipment_fabricat_id','=','equipment_fabricat.id');
$equipment->leftjoin('equipment_model','equipment_model_id','=','equipment_model.id');
$equipment->leftjoin('equipment_category','equipment_category_id','=','equipment_category.id');
$equipment->addSelect('equipment.*', DB::Raw("(SELECT count(ordre.status = 001) FROM ordre LEFT JOIN checklist ON checklist.ordre_id=ordre.id WHERE checklist.equipment_id=equipment.id and ordre.status = 001 order by ordre.start_date desc limit 1) AS orderStatusWithoutOpen"));
$equipment->orderBy($orderby,$order);
$equipment->groupBy('equipment.id');
$equipment->with('customer', 'fabricat', 'model', 'category', 'type','check','certificate');
$start = date('Y-m-d');
$end = date('Y-m-d', strtotime('+15days'));
if($filter == '1' && $filter != "") {
$equipment->having('orderStatusWithoutOpen', "<", $filter);
}
if($filter == $start) {
$equipment->whereBetween('certificate.valid_date', [$start, $end]);
}
if($id) {
$equipment->where('equipment.customer_id', '=', $id);
}
return $equipment->paginate(10);
}
Can anyone please tell me what mistake I'm making here.
Thanks in advance.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire