title: How do I efficiently limit query to the presence of an id in a many-to-many relationship in laravel?
body: <p><strong>The scenario:</strong></p>
  
  <p>I am building a search system for a given set of data. Most of this is straight forward - <em>where record title contains X, where record date before Y</em>, 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):</p>
  
  <pre><code>if(!empty($category)) {
   $records = $records-&gt;whereHas('categories', function($query) use ($category) 
    {
     $query-&gt;where('category_id', $category);
    });
  }
  </code></pre>
  
  <p>This <strong>works</strong>, 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. </p>
  
  <p>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. </p>
  
  <p><strong>The Question</strong>
  How do I efficiently limit the records returned by $records->get() to those records with a defined relationship to category $category.</p>
  
  <p><strong>Edit 2018-01-16</strong></p>
  
  <p>To clarify, while I could simply do $category->records to return all records belonging to a category, this is part of a larger search engine. The full structure of the code looks like this:</p>
  
  <pre><code>If($subject_search_term) {
     $records-&gt;where('subject', $subject_search_term)
  }
  
  If(some other search criteria is defined) {
     $records-&gt;where(someothercriteria);
  }
  
  If(Category search criteria is defined) {
     $records-&gt;whereHas(something);
  }
  
  $records-&gt;paginate(20);
  </code></pre>
  
  <p>Furthermore, there are two of these many-to-many relationships that I need to query (in addition to 'categories', lets say there is also a 'subject' that is independent of it, but similar structure and idea). As far as I know, I need to build the query off records and filter it accordingly.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>For anyone else with this problem, it seems the vastly more efficient way (and the most efficient that I've found) is Joel Hinz's comment - use the DB facade to build a raw query, pluck the id's from it, and use that in a whereIn clause.</p>