title: Rails Postgres query to exclude any results that contain one of three records on join
body: <p>This is a hard problem to describe but I have Rails query where I join another table and I want to exclude any results where the join table contain one of three conditions.</p>
  
  <p>I have a <code>Device</code> model that relates to a <code>CarUserRole</code> model/record. In that CarUserRole record it will contain one of three <code>:role</code> - "owner", "monitor", "driver". I want to return any results where there is no related CarUserRole record where <code>role: "owner"</code>. How would I do that?</p>
  
  <p>This was my first attempt - </p>
  
  <pre><code>Device.joins(:car_user_roles).where('car_user_roles.role = ? OR car_user_roles.role = ? AND car_user_roles.role != ?', 'monitor', 'driver', 'owner') 
  </code></pre>
  
  <p>Here is the sql - </p>
  
  <pre><code>"SELECT \"cars\".* FROM \"cars\" INNER JOIN \"car_user_roles\" ON \"car_user_roles\".\"car_id\" = \"cars\".\"id\" WHERE (car_user_roles.role = 'monitor' OR car_user_roles.role = 'driver' AND car_user_roles.role != 'owner')"
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p>I should mention that a device sometimes has multiple CarUserRole records. A device can have an "owner" and a "driver" CarUserRole. I should also note that they can only have one owner.</p>
  
  <p><strong>Anwser</strong></p>
  
  <p>I ended up going with @Reub's solution via our chat - </p>
  
  <pre><code>where(CarUserRole.where("car_user_roles.car_id = cars.id").where(role: 'owner').exists.not)
  </code></pre>