How do I check if column is null within query with eloquent?

Hi everyone!

I have a small issue. I am trying to delete all entries which have not been viewed in the past 30 days and were created more than 30 days ago.

The column last_viewed_at's default value is null, so in case the entry was created_at 5 days ago and was not viewed, the row should get not be deleted.

I currently use

$medias = Media::where('created_at', '<=', $delete_after_x_days)
    ->where('last_viewed_at', '<=', $delete_after_x_days)
    ->orWhere('last_viewed_at', null)
    ->get();

This code snippet does not work, because it would also match entries that were created within the last 30 days but there last_viewed_at value is null.

Somehow, I think I need to have a nested if query that checks first if last_viewed_at has a value.

Any help would be greatly appreciated!

Thanks so much!

Chris

bunnypro

9 years ago

Best Answer

Level 7

what about

Media::where('created_at', '<=', $delete_after_x_days)
->where(function ($query) {
    $query->where('last_viewed_at', '<=', $delete_after_x_days)
        ->orWhereNull('last_viewed_at');
})
->get();

2 likes

Chris1904

9 years ago

Level 15

I modified it like this and it worked :)

$medias = Media::where('created_at', '<=', $delete_after_x_days)
    ->where(function ($medias) use ($delete_after_x_days) {
        $medias->where('last_viewed_at', '<=', $delete_after_x_days)
            ->orWhereNull('last_viewed_at');
    })
    ->get();

Thanks so much!

Please or to participate in this conversation.