CODEWITHSUNDEEP
phpmysqllaraveleloquentpivot
Tallgeese
Tallgeese

Reputation: 75

How to use CASE WHEN in Eloquent ORM?

How to use CASE WHEN in Eloquent ORM?

I'm wondering if it will work if add

$query->selectRaw('CASE WHEN (created_at = '.$daterange.') THEN 'P' as attendance) something like that, but i don't know how to use CASE WHEN is it possible?

    // Create a loop for date range
    $daterange = [];

    for ($day = $search_from; $day <= $search_to; $day++) {
        $daterange[]['date'] = $this->carbon->parse($day)->format('M d');
    }

    // Search Student       
    $students = User::has('userStudentAttendance')->with(['userStudentAttendance' => function($query) use ($details,$search_from, $search_to){
        $query->where('status', $details);
        $query->where(function($query) use ($search_from, $search_to){
            $query->whereBetween(DB::raw('Date(created_at)'), [$search_from, $search_to]);
        });
        $query->groupby('user_id')->groupby(DB::raw('Date(created_at)'));
    }])
    ->leftjoin('sys_user_student', 'sys_user_student.user_id', '=', 'sys_user.user_id')
    ->leftjoin('sys_mf_section', 'sys_mf_section.section_id', '=', 'sys_user_student.section_id')
    ->leftjoin('sys_mf_grade', 'sys_mf_grade.grade_id', '=', 'sys_mf_section.grade_id')     
    ->where('user_type_id', '4')
    ->where('sys_mf_section.section_id', $request->getParam('section_id'))
    ->where('sys_mf_grade.grade_id', $request->getParam('grade_id'))    
    ->orderBy('sys_user.last_name') 
    ->get();

This is the look of the report that i want to achieve:

+--------------------------------------------------+
|student ID|full Name|Oct 23|Oct 24|Oct 25 | Oct 26|
|1         |stud 1   | P    | P    | P     | A     |
|2         |stud 2   | P    | A    | P     | P     |
|3         |stud 3   | P    | P    | P     | P     |
|4         |stud 4   | P    | P    | P     | A     |
|5         |stud 5   | A    | A    | A     | A     |
+--------------------------------------------------+

Upvotes: 1

Views: 9376

Answers (1)

Derta
Derta

Reputation: 31

I guess you are using Laravel. Maybe you could use select raw query provided by Laravel.

->select('users.id AS user_id', 'users.name',
DB::raw('(CASE WHEN users.status = 0 
THEN "active" WHEN users.status = 1 
THEN "not active" 
ELSE "pending" END) AS 
status_user'))
->orderBy('shares.created_at', 'desc')

Upvotes: 3

Related Questions