title: Sql query to retrieve all records along with relation ordered by foreign key with Laravel
body: <p>I have 2 tables named as 'genre' and 'preferred_genre', there is a foreign key in 'preferred_genre' table as 'genre_id' and it also have a 'user_id' column, i want to retrieve all the genre's but first those genre which has foreign key relation in 'preferred_genre' table, and is preferred by the logged in user.</p>
  <blockquote>
  <p>genre -&gt; id,genre_name</p>
  <p>preferred_genre -&gt;id, genre_id, user_id</p>
  </blockquote>
  <pre><code># genre table #
  ===============
  id | genre_name 
  ---------------
  1  | action    
  2  | comedy
  3  | horror
  4  | drama
  
  # preferred_genre table #
  =========================
  id | genre_id | user_id 
  ------------------------
  1  |     2    |   5
  2  |     4    |   5
  3  |     1    |   8
  4  |     4    |   8
  </code></pre>
  <p>let the logged in user id is 5</p>
  <pre><code>$q = Genre::select('genres.*');
  $q-&gt;leftJoin('preferred_genres', function($join)
  {
     $join-&gt;on('genres.id', '=', 'preferred_genres.genre_id');
     $join-&gt;where('user_id','=',Auth::id());
  });
  $genre_list = $q-&gt;orderBy('preferred_genres.created_at', 'desc')-&gt;get();
  </code></pre>
  <p>I want to get all the genres but the genre preferred by user_id 5, first, followed by all other genre, as follows</p>
  <pre><code>id  | genre_name
  ----------------
  2   | comedy
  4   | drama
  1   | action
  3   | horror
  </code></pre>