CODEWITHSUNDEEP
laravellaravel-4eloquent
Nur Uddin
Nur Uddin

Reputation: 2958

Conditional Select Statement in laravel eloquent

I have a raw query like that

SELECT IF(`user_group` = '1', `total_score`, `score`) FROM `user`

Now how I can convert this query in laravel eloquent ORM

Upvotes: 4

Views: 41408

Answers (3)

Mihir Bhende
Mihir Bhende

Reputation: 9055

Applicable if you need to have conditional join and select :

Using mysql's native conditionals can be a good way. You might be in a situation where if a particular condition is truthy in PHP then you need to join that table otherwise do not join.

For example :

If $loggedInUser is admin, then you want to get student attendence otherwise just show marks.

you can have(PS below is a pseudo code just for reference) : 

<?php 

// Having the column selection only when a particular condition is true
// Else have its value as NULL(You can have NA also)
if($loggedInUser->role == 'admin'){
    $attendanceColumnSelect = DB::raw('attendance.total as total_attendance');
}
else{
    $attendanceColumnSelect = DB::raw('NULL as total_attendance');
}
// Students query with joins which must be there always
$studentsQuery= Students::select('name', 'class', 'age', $attendanceColumnSelect)
                ->join('someothertable', 'someothertable.student_id', '=', 'student.id');

// Adding join of attendance only when required for admin role
if($loggedInUser->role == 'admin'){
    $studentsQuery->join('attendance', 'attendance.student_id', '=', 'student.id');
}
// Getting final data
$finalResult = $studentsQuery->get();

?>

If you try to do this way :

<?php 

$finalResult = DB::select("
    SELECT  students.name, 
            students.class, 
            students.age, 
            IF('$loggedInUser->role' = 'admin', attendance.total, NULL) as total_attendance
    FROM students
    INNER JOIN someothertable on someothertable.student_id = student.id
    INNER JOIN attendance on attendance.student_id = student.id
");
?>

Then you have to have the attendance join even when you know the condition is false because otherwise it will have 'unknown column attendance.total' error.

From my perspective, if we know we do not want a particular column, I would just not join that table. If you do an EXPLAIN on above raw query, you will find MySQL will need attendance table even when the If condition in select is false.

Please feel free to comment if you find this incorrect or any better suggestions.

Upvotes: 1

waseem asgar
waseem asgar

Reputation: 742

Convert the MYSQL CASE INTO LARAVEL Query

$query = DB::raw("(CASE WHEN user_group='1' THEN 'Admin' WHEN user_group='2' THEN 'User' ELSE 'Superadmin' END) as name");

and simply execute this query in 

DB::table('tablename')->select($query)->get();

or

YourModelClass::select($query)->get();

You will get the result.

Upvotes: 12

Sagar Rabadiya
Sagar Rabadiya

Reputation: 4321

DB::table('users')->select('IF(`user_group` = '1', `total_score`, `score`)')->get();

this will work

Upvotes: 0

Related Questions