Bdtjtk

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 IlluminateDatabaseQueryExpression as raw;

use projectModelsSubject;

use projectModelsSemester;

use projectModelsStudentRegistration;



$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

    ]));

});

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();

You just need to use like this way,

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