Eloquent queries: null checks, exists, count and columns

Four questions come round again and again as soon as you write Eloquent queries: is this column null, does this record exist, how many are there, and how do you fetch only the columns you need. This guide covers all four, with the SQL actually emitted and the pitfalls that cost you one query too many.

This article is part of the web development path. Every example was run on Laravel 13.30.1 with PHP 8.4.25, against a tasks table holding the columns id, title, assignee (nullable), active, priority and the timestamp columns, whose absence causes an error on the very first save, and whose conversion into Carbon objects is automatic.

Testing whether a column is null: whereNull and whereNotNull

In SQL you write IS NULL and IS NOT NULL, because = NULL never returns true. Eloquent exposes both conditions as methods:

// the tasks with no assignee
Task::whereNull('assignee')->get();

// the tasks that have one
Task::whereNotNull('assignee')->get();

The SQL produced is exactly what you would expect:

select * from "tasks" where "assignee" is not null

The same thing works on the query builder, without going through a model:

use Illuminate\Support\Facades\DB;

DB::table('tasks')->whereNull('assignee')->get();

You can pass an array to test several columns at once, the condition then applying to all of them:

Task::whereNull(['assignee', 'closed_at'])->get();

The variants follow the usual builder logic, with or and with negation:

Task::whereNull('assignee')->orWhereNotNull('closed_at')->get();

Testing whether a record exists

To find out whether a row matches, do not go and fetch it. exists() asks the database for a boolean:

if (Task::where('title', 'Publier')->exists()) {
    // at least one row matches
}

if (Task::where('title', 'Publier')->doesntExist()) {
    // no rows at all
}

The difference with count() shows up in the query log. Here is what the three forms actually send to the database:

-- exists()
select exists(select * from "tasks" where "active" = ?) as "exists"

-- count() > 0
select count(*) as "aggregate" from "tasks" where "active" = ?

-- get()->isNotEmpty()
select * from "tasks" where "active" = ?

All three answer the question, but the third one fetches every row and hydrates it into an object, only to throw the contents away straight afterwards. On a large table, that is the difference between a query of constant cost and one that grows with the data.

Creating only if the record does not exist

The “check, then insert” pair has a name: firstOrCreate(). The first array does the searching, the second supplies the values used only when a record is created.

$post = Post::firstOrCreate(
    ['slug' => $slug],                                  // search criteria
    ['title' => $title, 'body' => $body],               // used only when creating
);

If the row already exists, the second array is ignored: the record comes back as it is, with no write. Two variants round out the family:

// same logic, but without saving to the database
$post = Post::firstOrNew(['slug' => $slug], ['title' => $title]);
$post->exists;   // false until you call save()

// finds and updates, or creates
$post = Post::updateOrCreate(['slug' => $slug], ['title' => $title]);

Counting, and spotting an empty collection

Two different count() methods need telling apart. On the query builder it is a COUNT(*) run by the database. On a collection that is already loaded, it is a plain count in memory.

// COUNT(*) in the database, no rows fetched
Task::where('active', true)->count();      // 3

// the rows are loaded, then counted in PHP
$tasks = Task::where('active', true)->get();
$tasks->count();                           // 3

If you need the rows anyway, the second form is the right one: do not fire off a counting query on top. If all you want is the number, take the first.

To test whether a collection is empty, two explicit methods:

$tasks = Task::where('title', 'Inexistant')->get();

$tasks->isEmpty();      // true
$tasks->isNotEmpty();   // false

Note in passing that isEmpty() takes no argument. You often come across $posts->isEmpty($posts), which works by accident because the argument is ignored.

In a Blade view, @forelse handles the empty case without an explicit condition:

@forelse ($tasks as $task)
    <li>{{ $task->title }}</li>
@empty
    <li>Aucune tâche pour le moment.</li>
@endforelse

Selecting only the columns you need

By default, Eloquent does a select *. On a wide table, or when you only display two fields, that is transfer and hydration for nothing. Three forms let you restrict the columns:

// 1. select() on the builder
Task::select('title', 'priority')->where('active', true)->get();

// 2. as an argument to get() or first()
Task::where('active', true)->get(['title']);
Task::where('assignee', 'Damien')->first(['title', 'priority']);

// 3. adding to an existing selection
Task::select('id')->addSelect('title')->first();

When you only want one value, two methods spare you a full model:

// a single scalar value
Task::where('assignee', 'Damien')->value('title');   // 'Écrire le guide'

// a list of values
Task::where('active', true)->pluck('title');         // ['Écrire le guide', 'Relire']

// a list keyed by another column
Task::pluck('title', 'id');                          // [1 => 'Écrire le guide', …]

value() is a clear improvement on the first(['title'])->title written out of habit: it returns the string directly, and null when no row matches, where the other form errors on a null object.