TomNeal
1 month ago

Quickly counting records with Eloquent

Posted 1 month ago by TomNeal

Hi,

I display in my application a count of the records that exist within a table. When the user changes an input i.e. selects a different start date, it sends an ajax request to the controller below to get an updated estimated count based on the user's selection. The idea is that they can see how many records exist based on their selected parameters.

I filter the query using three user selected options: date_start, date_end and sensor_id. This method filters the result:

public static function queryWithParameters($parameters) {
        $sensor_data = SensorData::query();
        $sensor_data = $sensor_data->with(['sensor'])
           ->whereBetween('created_at', [$parameters['date_start'], $parameters['date_end']]);
        if((int)$parameters['sensor_id'] !== 0) {
            $sensor_data = $sensor_data->where('sensor_id', $parameters['sensor_id']);
        }
        return $sensor_data;
 }

I use this method elsewhere to return data for a csv export, however, for this I use in a controller and return the count of the result:

public function index() {
        $sensor_data = SensorData::queryWithParameters(request());
        return response()->json($sensor_data->count());
 }

This all works as intended, however, this table is expected to eventually contain millions of rows. WIth 3.2m rows, it takes about 2 seconds to update the count. Is there a better way of writing this so that the query returns a quicker count? We are only really after an estimate of what is there!

Hope that makes sense - thank you for your help!

Please sign in or create an account to participate in this conversation.