lundi 14 novembre 2016

Alias name in having clause in laravel 5.1

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