Collection::where() loaded your whole table to filter it

One method name, two entirely different operations

This is a method call that looks like it filters a table:

$slow = Article::all()->where('category', 'PERFORMANCE')->count();

And this one does:

$fast = Article::where('category', 'PERFORMANCE')->count();

Both return 3. Watch the SQL and the memory:

PHP-side filter: 3 rows | memory +1,388 KB | sql: ["select * from `articles`"]
SQL-side filter: 3 rows | memory +1 KB    | sql: ["select count(*) as `aggregate` from `articles` where `category` = ?"]

The first one selected every column of every row, hydrated each into a model, and then threw away 88% of them in PHP to produce an integer. That is 1,388KB to count to three, on a table with 25 rows. The second asked the database for the integer.

Article::all() returns an Illuminate\Database\Eloquent\Collection. From that point on, every chainable method you call has nothing to do with Eloquent — you are calling the collection API, which operates on an array that is already in memory. where() on a query builder is a SQL fragment. where() on a collection is a loop. They share a name and nothing else.

The reason this is so easy to write is that nothing distinguishes them at the call site. There is no type error, no deprecation, no slow-query log entry — the query is select * on an indexed table, which looks perfectly healthy. The only symptom is memory and time, and both scale with the size of the table rather than the size of the result.

The boundary is wherever the collection starts

all() is the obvious offender, but it is not the common one. The common one is a get() placed too early, usually because the query was built in one method and filtered in another:

$articles = Article::query()->get();         // the boundary is here

$recent = $articles->where('category', 'TESTING')
    ->sortByDesc('published_at')
    ->take(10);

Everything after get() is PHP. The SQL makes that unambiguous:

Article::all()->firstWhere('category', 'TESTING');
Article::firstWhere('category', 'TESTING');

One reads the table; one asks for a row:

select * from `articles`
select * from `articles` where `category` = ? limit 1

The same split runs through every pair you are likely to reach for. Sorting:

Article::all()->sortByDesc('id')->take(5);
Article::orderByDesc('id')->limit(5)->get();

Again, one of these sorts in PHP:

select * from `articles`
select * from `articles` order by `id` desc limit 5

And plucking, where the difference is columns rather than rows:

Article::all()->pluck('title');
Article::pluck('title');

And the column list gives it away:

select * from `articles`
select `title` from `articles`

That last pair is the cheapest win in the article. Article::pluck('title') selects one column. Article::all()->pluck('title') selects every column — including, on this site's articles table, the rendered body_html of every row — and then discards all but one. The call site differs by five characters.

The rule that covers all of it: narrow before you materialise. Filtering, sorting, limiting and column selection are all things the database does with an index and returns less data for. Do them in the query. Reach for collection methods when the work genuinely cannot be expressed in SQL — grouping into a shape a view needs, mapping to a DTO, formatting — and when the set you are operating on is already as small as you can make it.

Collection::where() compares loosely, and null equals zero

Performance is the half of this people find eventually, because the page is slow. The other half is a correctness bug that never announces itself.

Collection::where() uses loose comparison. The docs say so plainly, and it means the method does not do what a SQL where does:

$stock = collect([
    ['sku' => 'A1', 'qty' => 0],
    ['sku' => 'A2', 'qty' => '0'],
    ['sku' => 'A3', 'qty' => null],
    ['sku' => 'A4', 'qty' => '10'],
    ['sku' => 'A5', 'qty' => 10],
]);

Filter that for a quantity of 10, then for a quantity of 0, and compare each against its strict counterpart:

where('qty', 10)        -> A4,A5
whereStrict('qty', 10)  -> A5
where('qty', 0)         -> A1,A2,A3
whereStrict('qty', 0)   -> A1
whereNull('qty')        -> A3

The first line is usually harmless and occasionally what you wanted: '10' and 10 match, which papers over a column that arrives as a string from one driver and an integer from another.

The third line is the bug. where('qty', 0) matched the row whose quantity is null. In PHP, null == 0 is true, so a filter for "zero stock" silently includes "stock unknown". Swap in a real domain: a filter for where('balance', 0) picks up every account whose balance has not been calculated yet, and a dashboard that reads "142 customers with zero balance" is wrong in a direction nobody audits. The equivalent SQL, where balance = 0, excludes NULL — because in SQL, NULL = 0 is not true but unknown. So the moment the filter moves from the query into the collection, its meaning changes.

whereStrict() is the fix when you want identity, whereNull() and whereNotNull() when you want to ask about nullity explicitly, and a comparison operator when you want a range:

$stock->whereStrict('qty', 0);
$stock->whereNull('qty');
$stock->where('qty', '>', 5);

The same looseness runs through the sibling methods, and contains() is the one that bites hardest because it reads like a set membership test:

collect([1, 2, 3])->contains('2');       // true
collect([1, 2, 3])->containsStrict('2'); // false

An authorisation check written as $allowedIds->contains($request->input('id')) is comparing an integer array against a string from the request, and loose comparison is doing you a favour there right up until the day it is not. If a comparison matters, say which kind you meant.

What to change

Grep for the boundary rather than the symptom. These three patterns are each an unconditional full-table read followed by work the database could have done:

grep -rn -e "::all()->" app/ resources/views/
grep -rn -e "->get()->where" -e "->get()->sortBy" -e "->get()->pluck" -e "->get()->filter" app/
grep -rn -e "->get()->count()" -e "::all()->count()" app/

For each hit, move the operation before the get(). ->get()->count() becomes ->count(), ::all()->pluck('x') becomes ::pluck('x'), a collection sortBy becomes orderBy. Where a filter has to stay in PHP because it depends on something the database does not know, check what it is comparing: if the column is nullable and you are matching against 0, '' or false, you have the null == 0 bug today and should be calling whereStrict() or whereNull().

Then handle the case where the set is genuinely too large to hold at all — an export, a backfill, a report over every row. Narrowing the query is not enough there, and the method you pick matters more than you would expect; this site covers that choice separately in the piece on exports that still run out of memory after you add chunk().