title: Laravel Eloquent ORM many-to-many Search Query
body: <p>I am using Laravel v3, with Eloquent, and a MySQL database for this application </p>
  
  <p>I have an employee database, with a search form that displays a list of the employees that match the search criteria. You can fill out one or many fields, and it will match on all the filled out fields only. As seen below:
  <a href="http://i.stack.imgur.com/Ttfhr.png" rel="nofollow">http://i.stack.imgur.com/Ttfhr.png</a></p>
  
  <p>The search all functions correctly, except for I just added the multiselect for the "Training". An employee can have zero to many training certificates attached to them. If the user was to search for first name "Matt", and check off "CSTS" under the training search criteria, the list would show all employees that have a first name similar to Matt, and also have CSTS training. </p>
  
  <p>Database schema is as follows:</p>
  
  <pre><code>user
  --id
  --first_name
  --last_name
  --job_title_id
  etc...
  
  
  training
  --id
  --name
  --issused_date
  --expiry_date
  
  user_training
  --id
  --user_id
  --training_id
  </code></pre>
  
  <p>The Model's for User and Training are as follows:</p>
  
  <pre><code>class User extends Eloquent {
  
    public static $table = 'user';
  
    public function training(){
      return $this-&gt;has_many_and_belongs_to('training','user_training');
    }
  
  
  class Training extends Eloquent {
  
      public static $table = 'training';
  
  
    public function users(){
        return $this-&gt;has_many_and_belongs_to('user','user_training');
    }
  
  
  
  }
  </code></pre>
  
  <p>I have the following code, for the function search, and what I tried for the training:</p>
  
  <pre><code>Route::post('(:bundle)/list', function() {
  
  $search = new StdClass(); 
  $search-&gt;first_name = Input::get('first_name');
  $search-&gt;last_name = Input::get('last_name');
  $search-&gt;job_titles = Input::get('job_titles'); // array of ids/returns null if none selected
  $search-&gt;training = Input::get('training'); // array of ids/returns null if none selected
  $search-&gt;city = Input::get('city');
  $search-&gt;province = Input::get('province');
  $search-&gt;phone = Input::get('phone');
  $search-&gt;status = Input::get('status');
  $search-&gt;gender = Input::get('gender');
  $search-&gt;hire_from_date = Input::get('hire_from_date');
  $search-&gt;hire_to_date = Input::get('hire_to_date');
  $search-&gt;current_location = Input::get('current_location');
  $search-&gt;role = Input::get('role');
  
  Session::put('search', $search); // so we can access inside query builder sub-functions
  
  
  $query = User::where('active', '=', true);
  
  if(!empty($search-&gt;training)) { // one or many
  
  
      $query = User::with(array('training' =&gt; function($query) {
          $s_search = Session::get('search');
          //$query-&gt;where_in('training.id', $s_search-&gt;training);
          //foreach($s_search-&gt;training as $id) {
          //  $query-&gt;where('training.id', '=', $id);
          //}
      }));
  }
  
  
  //$query = User::join('user_training', 'user.id', '=', 'user_training.user_id');
  //$query-&gt;join('user_training', 'user.id', '=', 'user_training.user_id');
  //$query-&gt;join('training', 'training.id', '=', 'user_training.training_id');
  
  if(!empty($search-&gt;first_name)) { $query-&gt;where('first_name', 'LIKE', '%' . $search-&gt;first_name . '%'); }
  if(!empty($search-&gt;last_name)) { $query-&gt;where('last_name', 'LIKE', '%' . $search-&gt;last_name . '%'); }
  
  if(!empty($search-&gt;city)) { $query-&gt;where('city', 'LIKE', '%' . $search-&gt;city . '%'); }
  if(!empty($search-&gt;province)) { $query-&gt;where('province_id', '=', $search-&gt;province); }
  
  if(!empty($search-&gt;gender)) { $query-&gt;where('gender', '=', $search-&gt;gender); }
  if(!empty($search-&gt;phone)) { $query-&gt;where('phone_1', 'LIKE', '%' . $search-&gt;phone . '%'); }
  
  if(!empty($search-&gt;status)) { $query-&gt;where('status_id', '=', $search-&gt;status); }
  if(!empty($search-&gt;role)) { $query-&gt;where('role_id', '=', $search-&gt;role); }
  if(!empty($search-&gt;current_location)) { $query-&gt;where('location_id', '=', $search-&gt;current_location); }
  
  if(!empty($search-&gt;hire_from_date)) // "after"
      $query-&gt;where('hire_date', '&gt;=', date('Y-m-d', strtotime($search-&gt;hire_from_date)));
  
  if(!empty($search-&gt;hire_to_date))  // "before"
      $query-&gt;where('hire_date', '&lt;=', date('Y-m-d', strtotime($search-&gt;hire_to_date)));
  
  if(!empty($search-&gt;job_titles)) { // one or many
      $query-&gt;where(function($query){
          $s_search = Session::get('search');
          foreach($s_search-&gt;job_titles as $id) {
              $query-&gt;or_where('job_title_id', '=', $id);
          }
      });
  }
  
  
  
  $query-&gt;order_by('last_name');
  
  $user_list = $query-&gt;distinct()-&gt;get();
  $user_count = $query-&gt;count();
  
  var_dump($user_list); die;
  
  
  if(Input::get('action') == 'export') {
  
      if(Permission::check("Export Users CSV")) {
          $now = new DateTime();
          return Response::download(User::get_group_csv($user_list), date_format(new DateTime(), 'Y-m-d') . '_User_Export.csv');
      }
  
  } 
  </code></pre>
  
  <p>The "$user->training" comes in as a simple array of id's, which matches up with the Training->id table</p>
  
  <p>The Job Title search functions correctly with the multiselect, but the job_title_id is in the User table, not a relationship.</p>
  
  <p>I have tried looping, manually joining, using eager load, using Eloquent's "with", all with no success. Can someone point me in the right direction? </p>