use IFNULL in laravel

Question

I have a query in laravel which is working fine.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  'downloads.is_download' )
        ->orderBy('subjectID')
        ->get();

There is only one issue that is_download comes null when there is no relative entry in table. After some research I found that there is a function IFNULL by using which I can change is_download null to 0. So here is my query which is not working. Blank screen is showing.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();

I am able to write this query in my phpmyadmin but not know how to write in laravel

This is the api, So whole code looks like

use Illuminate\Database\Query\Expression as raw;
use project\Models\Subject;
use project\Models\Semester;
use project\Models\StudentRegistration;

$app->get('/api/getSubject/:studentID', function($studentID) use ($app) {
error_reporting(E_ALL);
    $currentUser = StudentRegistration::where('studentID', $studentID)->first();

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();
print_r($subjects);
    return $app->response->write(json_encode([
                'error' => 0,
                'subjects' => $subjects
    ]));
});

Md Mahfuzur Rahman · Accepted Answer · 2016-03-22 11:33:20Z

12

Try like this:

DB::Raw('IFNULL( `downloads`.`is_download` , 0 )');


$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
    ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
    ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  DB::Raw('IFNULL( `downloads`.`is_download` , 0 )') )
    ->orderBy('subjectID')
    ->get();

answered Mar 22, 2016 at 11:33

Md Mahfuzur Rahman

2,3693 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

silentcoder Over a year ago

blank screen. no change.

Md Mahfuzur Rahman Over a year ago

Did you use \DB::table() instead of $app->db->table() and \DB::Raw() ? This should work fine. Otherwise check your query again when you are using it in laravel.

Md Mahfuzur Rahman Over a year ago

In laravel documentation, I didn't see anything like this. So, it's better to use DB::table().

Dayachand Patel · Accepted Answer · 2018-12-29 08:42:14Z

5

You just need to use like this way,

DB::raw('IFNULL( downloads.is_download, 0) as is_download')

answered Dec 29, 2018 at 8:42

Dayachand Patel

4977 silver badges12 bronze badges

1 Comment

kinsley kajiva Over a year ago

this has helped me in my case thanks i had a scenario that required this case another other selections for example $colArray = [ 'column1' , 'otherColumn' ,DB::raw('IFNULL( downloads.is_download, 0) as is_download') ];

Agus Suhardi · Accepted Answer · 2020-05-27 05:41:02Z

0

This my code

->whereRaw('customer_id = IFNULL(?, customer_id)', [$request->customer_id])

answered May 27, 2020 at 5:41

Agus Suhardi

846 bronze badges

Collectives™ on Stack Overflow

use IFNULL in laravel

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related