Query model when column not null

Look at whereNotNull.

https://laravel.com/docs/7.x/queries#where-clauses

$this->trips()->whereNotNull('date_to')->where('date_to', '<=', Carbon::parse($date)->toDateString())->get();

Hi @dosmukhanbet

I understand that you want to get both the cases with NULL dates as well as those matching $date.

In that case, try changing your orWhere to orWhereDate and the Carbon expression to Carbon::parse($date)->toDateString() as pointed out by @michaloravec

So you'll have:

 $this->trips()->where( function ( $query ) use ( $date ) {
        $query->whereNull( 'date_to' )
              ->orWhereDate('date_to', '<=' , Carbon::parse($date)->toDateString());
    });

See: https://laraveldaily.com/eloquent-date-filtering-wheredate-and-other-methods/

@marianomoreyra am I reading wrong? I think he means it's a null allowed column, so he wants not null dates.

@jlrdw Exactly, it's a nullable column, but from his code I understand that he want to be able to get all the records that match that date, including null one.

Maybe it doesn't make sense, but as the expected results are not that clear, I'm giving the solution for that alternate scenario

don't forget to add ->get() to the query

You are right @snapey!!

Maybe that's the only thing he was missing to making it work! :)

$this->trips()->where('date_to', '<=' , Carbon::parse($date)->format('Y-m-d'))->get();

Should skip the null dates.

Hi, thank you for reply, your offered solution works fine👍

@michaloravec I've hurried up with reply, code does not work as I expect. Let me explain what is problem. There is table with column date_from and date_to, date_to column is optional, so it might be null. I want to query this column if it has value, if it is null not need to run query at all. Is there way to query conditionally depending column value?

trips()->with('user', 'a_point', 'b_point')
                                    ->where('date_from', '>=' , Carbon::today())
                                    ->where('date_from', '>=' , Carbon::parse($date)->format('Y-m-d'))
                                    ->where( function ( $query ) use ( $date ) {
                                        $query->whereNotNull( 'date_to' )
                                              ->orWhere('date_to', '<=' , Carbon::parse($date)->format('Y-m-d'));
                                    });

Just allow null. You will then get rows that have a date_to that is before or equal to the date, OR where there is no date set in database.

$query->whereNull( 'date_to' )
        ->orWhere('date_to', '<=' , Carbon::parse($date)->format('Y-m-d'));

Maybe like this

->where(function ($query) use ($date) {
    $query->where(function ($query) use ($date) {
        $query->whereNotNull('date_to')->where('date_to', '<=', Carbon::parse($date)->toDateString());
    })->orWhereNull('date_to');
});

By the way if you hurry up with something make another thread, because your question in this thread was something else.

@michaloravec this query does not work as well, in this case previous query where('date_from', '>=' , Carbon::parse($date)->format('Y-m-d') ignores, and return all datas that match latest query.

In both you compare same date so what do you think will happen?