title: Laravel 5.5 select users based on session with roles as
body: <p>I want to select all users of sessions where roles for the users are not admin or only roles where roles.name is user.</p>
  
  <p>my user table is </p>
  
  <p><a href="https://i.stack.imgur.com/HkUPc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HkUPc.png" alt="enter image description here"></a></p>
  
  <p>session table is
  <a href="https://i.stack.imgur.com/AfLEd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AfLEd.png" alt="enter image description here"></a></p>
  
  <p>and roles table is this</p>
  
  <p><a href="https://i.stack.imgur.com/0qTJD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0qTJD.png" alt="enter image description here"></a></p>
  
  <p>I am running this query and it is working fine but it selects all users including admin user. I only want to select user where roles.name is user</p>
  
  <pre><code>    $sessions = Sessions::where('user_id', '&lt;&gt;', 'NULL')
                          -&gt;join('users', 'users.id', 'sessions.user_id')
                          -&gt;select('users.first_name','users.last_name',
                              DB::raw('count(sessions.user_id) as counts'))
                          -&gt;get();
  </code></pre>