11

I have a scope to limit all questions by whether or not a user has voted on them. In the model:

scope :answered_by, lambda {|u| joins(:votes).where("votes.user_id = ?", u.id) }
scope :unanswered_by, lambda {|u| joins(:votes).where("votes.user_id != ?", u.id) }

In the controller, I call them like this:

@answered = Question.answered_by(current_user)
@unanswered = Question.unanswered_by(current_user)

The unanswered_by scope is incorrect. I essentially want to find where there is no vote. Instead, it is trying to look for if there is a vote that doesn't equal the current user. Any ideas how to return all records where a join doesn't exist?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Marc
  • 1,033
  • 2
  • 10
  • 28

3 Answers3

23

Use an EXISTS expression:

WHERE NOT EXISTS (
   SELECT FROM votes v  -- SELECT list can be empty
   WHERE  v.some_id = base_table.some_id
   AND    v.user_id = ?
   )

The difference

... between NOT EXISTS() (Ⓔ) and NOT IN() (Ⓘ) is twofold:

  1. Performance

    Ⓔ is generally faster. It stops processing the subquery as soon as the first match is found. The manual:

    The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.

    Ⓘ can also be optimized by the query planner, but to a lesser extent since NULL handling makes it more complex.

  2. Correctness

    If one of the resulting values in the subquery expression is NULL, the result of Ⓘ is NULL, while common logic would expect TRUE - and Ⓔ will return TRUE. The manual:

    If all the per-row results are either unequal or null, with at least one null, then the result of NOT IN is null.

Essentially, (NOT) EXISTS is the better choice in most cases.

Example

Your query can look like this:

SELECT *
FROM   questions q
WHERE  NOT EXISTS (
    SELECT FROM votes v 
    WHERE  v.question_id = q.id
    AND    v.user_id = ?
    );

Do not join to votes in the base query. That would void the effort.

Besides NOT EXISTS and NOT IN there are additional syntax options with LEFT JOIN / IS NULL and EXCEPT. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • not sure if there is a difference between yours and shweta's solutions, but also works – Marc Jan 10 '13 at 21:06
  • @Marc: I added a rationale for my suggestion. – Erwin Brandstetter Jan 10 '13 at 22:43
  • Thanks. I thought it worked, but ran some tests and it's not returning all the unanswered questions: SELECT "questions".* FROM "questions" INNER JOIN "votes" ON "votes"."question_id" = "questions"."id" WHERE (NOT EXISTS (SELECT 1 FROM votes WHERE user_id = ?)) – Marc Jan 11 '13 at 00:04
  • @Marc: I added a version of your query that should work to my answer. I also clarified the original code example to reflect that you want an answer *per row* in the base table. – Erwin Brandstetter Jan 11 '13 at 00:16
  • Hurray! `NOT IN` will never be more correct and can never be faster than the corresponding `NOT EXISTS`. essentially, `NOT IN` is evil. ( I just don't like the word *anti-pattern* ) – wildplasser Jan 11 '13 at 00:17
  • But, Captain @wildplasser! There *are* cases where you *want* the `NULL`. Only they are few and far between. – Erwin Brandstetter Jan 11 '13 at 00:19
  • There is an additional performance-issue: `NOT IN` has to remove duplicates (read: sort), including the dreaded NULLS. (smart planners (==simple plans) may know when not to sort, though) I still have to find a case where `IN` is actually needed. `NOT IN` is even worse ... (BTW: I think, I have a few `NOT IN`s in my sudoku solver. time for a rewrite ...) – wildplasser Jan 11 '13 at 00:21
2

And if you want to do EXISTS query in elegant and Rails-ish manner, you can use Where Exists gem I've written:

Question.where_not_exists(:votes, user_id: current_user.id)

Of course, you can made scope of it as well:

scope :unanswered_by, ->(user){ where_not_exists(:votes, user_id: user.id) }
EugZol
  • 6,476
  • 22
  • 41
1

try this and let me know if it works

EDIT-1

scope :unanswered_questions, lambda { joins('LEFT OUTER JOIN votes ON questions.id = votes.question_id').where('votes.question_id IS NULL') }

EDIT-2

scope :unanswered_by, lambda {|u| where("questions.id NOT IN (SELECT votes.question_id from votes where votes.user_id = ?)",u.id) }
shweta
  • 8,019
  • 1
  • 40
  • 43
  • I have updated the answer, now it will return all questions which have no answers – shweta Jan 10 '13 at 06:02
  • Seems to be closer. I'm trying to get unanswered questions for a particular user, which is the u variable. Any ideas how to check that the votes don't exist for a particular user? – Marc Jan 10 '13 at 13:17