vincent15000's avatar

Where clause with null values using MariaDB database

Hello,

I have this code and I don't understand why it doesn't show the line with a null payment_date.

public function scopeFilter(Builder $builder, array $filters)
{
    $builder
        ->when($filters['year'], function ($query, $value) {
            $query->where(function ($query) use ($value) {
                $query
                    ->whereYear('payment_date', $value)
                    ->orWhereDate('payment_date', null);
            });
        })
        ->when($filters['activity_id'], function ($query, $value) {
            $query->where('activity_id', $value);
        })
        ->when($filters['account_id'], function ($query, $value) {
            $query->where('account_id', $value);
        });
}

...

$transactions = Transaction::
    with('activity', 'account', 'paymode')
    ->filter($filters)
    ->orderByRaw('isnull(payment_date), payment_date')
    ->get();

When the year filter is null, I see the line with the null payment_date, but otherwise no. So the probem is in the filter, but where ?

What is wrong in my query ?

Thanks for your help.

V

0 likes
1 reply
vincent15000's avatar
vincent15000
OP
Best Answer
Level 63

I have found the problem.

public function scopeFilter(Builder $builder, array $filters)
{
    $builder
        ->when($filters['year'], function ($query, $value) {
            $query->where(function ($query) use ($value) {
                $query
                    ->whereYear('payment_date', $value)
                    ->orWhere('payment_date', null);
            });
        })
        ->when($filters['activity_id'], function ($query, $value) {
            $query->where('activity_id', $value);
        })
        ->when($filters['account_id'], function ($query, $value) {
            $query->where('account_id', $value);
        });
}

Please or to participate in this conversation.