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

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!

Answer

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