laravel sql query if and if condition

Question

If column1 is equal to null I want my column2 to be between two dates in relation to the month and day, and if column1 is not null I want that column (column1) to be between two dates in relation to the month and day, too. All columns are of type date.

I would use this sql query in laravel with tableName->whereRaw ("SQL query"), or directly with the laravel commands if anyone knows how.

I have this for now, it works individually, but not together.

SELECT * FROM tablename WHERE column1 IS NULL AND DATE_FORMAT(column2, '%m-%d') BETWEEN DATE_FORMAT('2005-01-01', '%m-%d') AND DATE_FORMAT('2020-12-30', '%m-%d') AND WHERE DATE_FORMAT(column1, '%m-%d') BETWEEN DATE_FORMAT('2005-01-01', '%m-%d') AND DATE_FORMAT('2020-12-30', '%m-%d')

OMR OMR · Accepted Answer · 2020-07-03T16:12:46

you could do it this way:

when column1 is not null get all while column1 is between .... else when column2 is not null get all while column2 is between ....

 $values=DB::table('table_name')->where(function (Builder $query){
            $query->whereNotNull('column1')->whereBetween('column1',[Carbon::parse('2005-01-01'),Carbon::parse('2020-12-30')]);
        })->orWhere(function (Builder $query){
            $query->whereNull('column1')->whereNotNull('column2')->whereBetween('column2',[Carbon::parse('2005-01-01'),Carbon::parse('2020-12-30')]);
        })->get();

please not that you don't have to format dates when using it in 'between'

laravel sql query if and if condition

1 Answers