[FIXED] Laravel - Using `SQL_CALC_FOUND_ROWS` with eager-loaded relationship returns wrong count

Issue

In my Laravel app I was doing an ordinary query on a model (ModelA) whilst making use of SQL_CALC_FOUND_ROWS and then performing SELECT FOUND_ROWS() afterwards to retrieve the count of all records since the first query used LIMIT and OFFSET.

This worked perfectly fine, but now that I've added a relationship to the model I was querying above, if I do the same query but using with->('modelB'), this query is performed after the initial query and before SELECT FOUND_ROWS() so I get the count of the ModelB results instead of ModelA as I was expecting.

Is there a way to make this work as desired where I get the count of the first (main) query and not the relationship?

e.g. This works fine:

$query = ModelA::select([DB::raw("SQL_CALC_FOUND_ROWS *")])
    ->where('active', 1);

// conditional ->where()'s

$query->skip(intval($skip))
    ->take(intval($take))
    ->orderBy($column, $dir);

$results = $query->get();

$total = DB::select(DB::raw("SELECT FOUND_ROWS() AS 'total';"))[0]->total;

but changing the first line to this doesn't:

$query = ModelA::with('modelB')
    ->select([DB::raw("SQL_CALC_FOUND_ROWS *")])
    ->where('active', 1);

A workaround is to just do it without eager-loading and fetch each relationship individually but then I'd have one query per result when I loop through the results later in the code.

Solution

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it...

Eloquent's eager loading will cause an extra select statement to be executed for each relationship in the with method.

Therefore FOUND_ROWS() is returning the count for the last SELECT statement, the eagerly loaded relation.

To get around this you can use lazy eager loading. Instead of:

$books = App\Book::with('author.contacts')->get();

Use:

$books = App\Book::all();
$count = DB::select(DB::raw('SELECT FOUND_ROWS()'));
$books->load('author.contacts');

Answered By - Brett Y