Get total number of records with Laravel?

Question

MySQL has a feature for getting the total number of records a query would return without a limit, SQL_CALC_FOUND_ROWS. Does Laravel support this?

Currently I have to do it in two queries:

public function dataTable() { $bookings = DB::table('bookings') ->limit(Input::query('iDisplayLength')) ->offset(Input::query('iDisplayStart')) ->get(); $count = $bookings = DB::table('bookings') ->count(); return Response::json([ 'iTotalRecords' => $count, ]); }

Not only will this be less efficient, but there's going to be a lot of redundant code once I add in all the ->where() criteria.

Do you want this solution for Laravel Datatable package? If it, there is a basic solution. — Yilmazerhakan
– Yilmazerhakan, Commented Apr 3, 2014 at 21:39
@Yilmazerhakan No thanks. That looks overly complex for my needs. I wouldn't mind knowing how they do the counts though -- are they all separate queries? — mpen
– mpen, Commented Apr 3, 2014 at 22:05

giaour · Accepted Answer · 2014-03-30 18:54:40Z

4

For any complicated or vendor-specific queries, you generally have to pass the query directly with DB::raw(), e.g.:

$bookings = DB::table('bookings') ->select(DB::raw('SQL_CALC_ROWS_FOUND ...

answered Mar 30, 2014 at 18:54

giaour

4,0712 gold badges27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mpen Over a year ago

Thanks for the tip, but I won't be able to use it like that. There can't be a comma after SQL_CALC_FOUND_ROWS, which Laravel adds because it thinks it's a regular column.

itachi Over a year ago

laravel doesn't add the comma.

mpen Over a year ago

@itachi: Well something is, because the SQL it produces is "select SQL_CALC_FOUND_ROWS, created_at, first_name, last_name from bookings limit 25 offset 0" which is an SQL error.

itachi Over a year ago

i tried DB::table('foo') ->select(array(DB::raw('SQL_CALC_FOUND_ROWS *'))) ->toSql(); it returns select SQL_CALC_FOUND_ROWS * from `foo`

mpen Over a year ago

Oh... I was doing ->select(DB::raw('SQL_CALC_FOUND_ROWS'),'created_at',...). I guess I could put all the rows in the raw bit.

kamlesh.bar · Accepted Answer · 2015-01-28 09:31:29Z

Refined what @giaour said.

$bookings = DB::table('bookings') ->select(array(DB::raw('SQL_CALC_FOUND_ROWS booking_id,name'))) ->where('...') ->orWhere('...') ->take(10) ->skip(50) ->get(); $bookingsCount = DB::select( DB::raw("SELECT FOUND_ROWS() AS Totalcount;") );

After SQL_CALC_FOUND_ROWS you can fetch count by SQL_CALC_FOUND_ROWS();

Paul · Accepted Answer · 2017-07-21 20:19:20Z

I came across this issue because I wanted to paginate a set of results which already included a GROUP BY and Laravel's built-in paginate() method uses COUNT(*) and doesn't work with GROUP BY.

So I ended up extending the Builder class to inject the SQL_CALC_FOUND_ROWS right before a query is run, and run FOUND_ROWS right after. I did this by overriding the Laravel getModels() method.

I've uploaded the code here.

Collectives™ on Stack Overflow

Get total number of records with Laravel?

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related