Switching to other field when the value is null using whereYear Laravel Query Builder

user_9567981 0
Viewed 409

I have question with regard to switching on other field when the second field is null using whereYear.

Say for example we have this database table

+------------+------------+
| field_one  | field_two  |
+------------+------------+
| 2020       | NULL       |
+------------+------------+

I want to switch to field_one when the value of field_two is null. It doesn't work using orWhere because orWhere switches to field one if the condition is not matched or false. I'm not really sure if orWhere is the solution for this.

$year = Carbon::now()->year;
$table = DB::table('my_table')
            ->where(function($table) use ($year) {
                $table->whereYear('year', '=', $year)
                      ->orWhereYear('year', '=', $year);
            })->count();

Thank you in advance!

laravel builder php
2 Answers

You can use a raw query combined with IFNULL() - whereYear() basically applies the MySQL function YEAR() to a datetime, and we can do that manually to combine it with IFNULL(), which selects the second column if the first one is NULL.

$year = Carbon::now()->year;
$table = DB::table('my_table')
            ->whereRaw("YEAR(IFNULL(field_one, field_two)) = ?", [$year])
            ->count();
user_45352000

I know this question is old but here is an answer if you want to keep it 100% eloquent :

DB::table('my_table')
        ->where(function($query) use ($year) {
            $query->whereYear('field_one', '=', $year)
                  ->whereNotNull('field_one')
                 // OR
                  ->orWhereYear('field_two', '=', $year)
                  ->whereNull('field_one');
        })->count();
user_146294580
Related