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

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.

sqlruby-on-railsrubydatabase


Solution 1:[1]

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?

Solution 2:[2]

The query doesn't work since .where(review_labels: {user_id: 9101 ... creates WHERE review_labels.user_id = 9101 AND review_labels.question_id = nil. The rows would have to meet both of those condition - not just the joined rows. If you wanted to do it though a join you would have to add the conditions to the join clause:

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

AR doesn't have a good way to use binds in join clauses so there are other solutions to the problem that are preferable.

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
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 tinazmu
Solution 2

Related Questions