How do I efficiently limit query to the presence of an id in a many-to-many relationship in laravel?

The scenario:

I am building a search system for a given set of data. Most of this is straight forward - where record title contains X, where record date before Y, etc. Where I am running into difficulty is essentially a category search. Each record belongs to zero or more categories (a relationship pivot table exists such that each row contains a record and a category), and when the user searches for Category A, I want to return all of the records that belong to that category. I've gotten this working with a whereHas, but it seems inordinately slow. In this instance, assume $category is a numeric id that is correctly validated as a category, and $records is an eloquent query builder that has not yet been executed by a get, pagination, all, etc (my function checks to see if several $request->input parameters are defined, then attaches a where to $record as required by the specified parameters, only executing it after all parameters have been considered):

if(!empty($category)) {
 $records = $records->whereHas('categories', function($query) use ($category) 
  {
   $query->where('category_id', $category);
  });
}

This works, but as there are 7000+ records, 7000+ relationships defined in the pivot, and roughly 30 'categories', the search takes longer than I am comfortable leaving it. My unconfirmed thought is that the where query is executing for every record, thus leading to hundreds or thousands of queries.

I've debated approaching this using the raw query builder and just passing the list of record id's that have that category and using a simple where to filter the record collection before it's executed, but it seems counter-intuitive, leading me to believe there must be a better way.

The Question How do I efficiently limit the records returned by $records->get() to those records with a defined relationship to category $category.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2D7FPbN
via IFTTT