laravel query builder from raw query

Question

Good day! I'am new to Laravel. I tried many ways to do it but still it gives me an error. I'm trying to convert this sql query into eloquent

 Select     t.employee_code, 
            CASE WHEN t.day = '2017-08-19' THEN t.PRESENT ELSE NULL END AS `2017-08-19`,
            CASE WHEN t.day = '2017-08-20' THEN t.PRESENT ELSE NULL END AS `2017-08-20`,
            CASE WHEN t.day = '2017-08-21' THEN t.PRESENT ELSE NULL END AS `2017-08-21`,
            CASE WHEN t.day = '2017-08-22' THEN t.PRESENT ELSE NULL END AS `2017-08-22`,
            CASE WHEN t.day = '2017-08-23' THEN t.PRESENT ELSE NULL END AS `2017-08-23`,
            CASE WHEN t.day = '2017-08-24' THEN t.PRESENT ELSE NULL END AS `2017-08-24`
FROM (
select e.employee_code, 
    Cast(e_l.time_in As date) As Day,
    Case 
        WHEN e_l.time_in IS NULL THEN 'A' 
        WHEN DAYOFWEEK(e_l.time_in) In(7, 1) Then 'W'
        ELSE 'P' 
    end as PRESENT
from employee As e
left join employees_logs As e_l on e.id = e_l.employee_id)
AS t

And I tried this eloquent way

public static function statusReport($data){
$start_date = $data['start_date'];
$end_date = $data['end_date'];
$date_array = self::getDatesFromRange($start_date, $end_date);

$query = DB::raw("(Select t.employee_code,
          CASE WHEN t.day = '$start_date' THEN t.Present ELSE NULL END AS '$start_date')");
$query->addSelect(
        DB::raw("(
            SELECT employee.id as emp_id, 
            CONCAT(employee.firstname, " ",employee.lastname) AS employee_name,
            CAST(employees_log.time_in as date) as date_given,
            CASE    WHEN employee_logs.time_in IS NULL THEN 'A'
                    WHEN leave.status_id = 4 AND leave_request.with_pay = 1  THEN 'L'
                    WHEN leave.status_id = 4 AND leave_request.with_pay = 0  THEN 'LOP'
                    WHEN DAYOFWEEK(employee_logs.time) In(7, 1) THEN 1 AS 'W' 
                    ELSE 'P' END as status
            LEFT JOIN employee_logs On employee.id = employees_logs.employee_id
            JOIN leave On employee.id = leave.emp_id
            JOIN leave_request On leave.id = leave_request.leave_id
            WHERE employee_logs.time_in BETWEEN '$start_date' AND '$end_date') As `t`"));
$data = $query->get();
return $data;

}

the expected output is this query will tell the status of employee in each day. For example if this day is Weekend it will show 'W' on the output. if the employee is Absent 'A' and if present 'P' . I hope someone can help me with this. Thanks in advance

syntax error, unexpected '"' in C:\xampp\htdocs\hrpayroll\api\app\Http\Models\AttendanceReportModel.php on line 178 — , Aug 24 '17 at 06:08
Why do you have two `DB::raw` statements, also you don't concatenate the result. I would say the error comes from there. — Alex Andrei, Aug 24 '17 at 06:16
I accidentally delete the addselect. thanks @alex. now edit my code. — , Aug 24 '17 at 06:23
Try to add exact code that you have on your page. I think you messed up with your **statusReport** function. You added it double times. — Saroj, Aug 24 '17 at 06:24
@404BrainNotFound I do apologize for the mess. and thanks for telling it. now this is the final code that im working — , Aug 24 '17 at 06:27

score 0 · Accepted Answer · answered Aug 24 '17 at 06:42

At first take a look at the addSelect method:

public function addSelect($column)
{
    $column = is_array($column) ? $column : func_get_args();
    $this->columns = array_merge((array) $this->columns, $column);
    return $this;
}

This just adds the column(s) by merging with existing selected columns.

For more clarification visit https://laravel.com/docs/5.4/queries#selects

Now I guess you will solve your problem without using addSelect method.

My Reference this

laravel query builder from raw query

1 Answers1