title: SQL Query with JOIN, WHERE and AND clause to convert in Laravel queries
body: <p>I'm having problem converting my SQL query to laravel queries. I want to join two tables <code>users</code> and <code>messages</code> so that i can get the users that has conversation with other users.</p>
  
  <p>Database Structure:</p>
  
  <p>Users:
  <a href="https://i.stack.imgur.com/GsWs8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GsWs8.png" alt="enter image description here"></a></p>
  
  <p>Messages:</p>
  
  <p><a href="https://i.stack.imgur.com/BZnAQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BZnAQ.png" alt="enter image description here"></a></p>
  
  <p>This is my SQL query where I get what I want:</p>
  
  <p><a href="https://i.stack.imgur.com/7JxEu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7JxEu.png" alt="enter image description here"></a></p>
  
  <pre><code>SELECT u.id, c.id, u.first_name, u.last_name FROM messages c, users u 
        WHERE (CASE WHEN c.user_one =  @USERID THEN c.user_two = u.ID WHEN c.user_two = @USERID THEN c.user_one= u.id END ) 
        AND ( c.user_one = @USERID OR c.user_two = @USERID ) Order by c.id DESC Limit 20
  </code></pre>
  
  <p>This is my Laravel query: </p>
  
  <pre><code>DB::table('messages')
     -&gt;join('users', function($join) use ($user_id) {
          $join-&gt;select(DB::raw('CASE WHEN messages.user_one = ' . $user_id . ' THEN messages.user_two = ' . $user_id . 'WHEN messages.user_two = ' . $user_id . ' THEN messages.user_one = ' . $user_id));
          $join-&gt;on(function($query) use ($user_id)
          {
               $query-&gt;where('messages.user_one', '=', $user_id);
               $query-&gt;orWhere('messages.user_two', '=',$user_id);
          });       
     })
     -&gt;select('users.*', 'messages.*')
     -&gt;get();
  </code></pre>