Get the records from a table which id does not exist in another table for a specific User-CodePudding

I've Two tables:

table name         column names
-----------        -------------
question           id | name | description
review_labels      id | question_id | user_id | review_label

I have a user_id (example: 9101) Now I want to extract the questions from question table of which the question_id doesn't exist in review_labels table for user 9101.

example:

Question table

id  |  name  | description
 1  |  ....  | .... 
 2  |  ....  | ....
 3  |  ....  | ....
 4  |  ....  | ....
 5  |  ....  | ....
 6  |  ....  | ....

table ReviewLabel

id | question_id | user_id | review_label
 1 |           1 |   9432  |   1
 2 |           3 |   9442  |   5
 3 |           1 |   9101  |   4
 4 |           4 |   9101  |   5
 5 |           4 |   9432  |   4
 6 |           6 |   9432  |   4

The result of the query should be

id  |   name  | description
 2  |   ....  | ....
 3  |   ....  | ....
 5  |   ....  | ....
 6  |   ....  | ....

I tried this following query:

Question.left_outer_joins(:review_labels).where(review_labels: {user_id: 9101, question_id: nil})

It create the following sql:

SELECT `questions`.* FROM `questions` LEFT OUTER JOIN `review_labels` ON `review_labels`.`question_id` = `questions`.`id` WHERE `review_labels`.`user_id` = 9101 AND `review_labels`.`question_id` IS NULL

Unfortunately the result is an empty list. I can't understand what should I do to solve this problem.

CodePudding user response：

Don't know ruby-on-rails but in SQL NOT EXISTS suits your problem better:

SELECT `questions`.* 
FROM `questions` Q
WHERE NOT EXISTS 
     (SELECT 1  
      FROM `review_labels` RL 
      WHERE  RL.`question_id` = Q.`id` 
       AND RL.`user_id` = 9101
      )

Can you try?

CodePudding user response：

The most straight forward way is a WHERE id NOT IN (subquery):

Question.where.not(
  id: ReviewLabel.select(:question_id).where(
    user_id: 9101 
  )
)

Another way of doing this is a NOT EXIST subquery like in Tinamzu's answer:

Question.where(
  ReviewLabel.where(
    user_id: 9101
  ).where(
    ReviewLabel.arel_table[:question_id].eq(Question.arel_table[:id])
  ).arel.exists.not
)