在laravel中使用IFNULL [英] use IFNULL in laravel

问题描述

我在 laravel 中有一个查询，效果很好.

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

当表中没有相关条目时， is_download 为空只有一个问题.经过一些研究，我发现有一个函数 IFNULL 可以通过它将 is_download null 更改为 0 .所以这是我的查询不起作用.显示空白屏幕.

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

我能够在phpmyadmin中编写此查询，但不知道如何在 laravel

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

这是api，所以整个代码看起来像

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
    ]));
});

推荐答案

尝试如下:

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

这篇关于在laravel中使用IFNULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！