Menu
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
]));
});
710
$getId = DB::table('Customers')->select(['*', DB::raw('IFNULL(id, 0)')])->first(); Right now the second argument of select is being used to replace variables which brakes the query.
You can check if the column value of a record is null or not in laravel using whereNotNull() and exists() method. exists() method returns false if the column value of a record in the table is null and it returns true if column value is not null.
Check if not null: whereNotNullSELECT * FROM users WHERE last_name IS NOT NULL; The equivalent to the IS NOT NULL condition in Laravel Eloquent is the whereNotNull method, which allows you to verify if a specific column's value is not NULL .
You just need to use like this way,
DB::raw('IFNULL( downloads.is_download, 0) as is_download')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
