Articles Snippets Projects Uses

Query Laravel Eloquent Model by a Nullable Date Range

use App\Models\SomeModel;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;

SomeModel::query()
          ->where(function (Builder $publishedAtQuery) {
              $publishedAtQuery->where(function (Builder $query) {
                  $query->whereNull('published_at');
              })->orWhere(function (Builder $query) {
                  $query->where('published_at', '<=', Carbon::now()->startOfDay());
              });
          })
          ->where(function (Builder $expiredAtQuery) {
              $expiredAtQuery->where(function (Builder $query) {
                  $query->whereNull('expired_at');
              })->orWhere(function (Builder $query) {
                  $query->where('expired_at', '>=', Carbon::now()->startOfDay());
              });
          })
          ->orderBy('created_at')
          ->get();
SELECT
	*
FROM
	`some_models`
WHERE
	and((`published_at` IS NULL)
	or(`published_at` <= 2021 - 07 - 28 00:00:00))
	and((`expired_at` IS NULL)
	or(`expired_at` >= 2021 - 07 - 28 00:00:00))
ORDER BY
	`created_at` ASC