Use activerecord to find records that are not in many-to-many relation for specific user (ruby-on-rails)

Question

Ok, my database contains three tables: users, words, statuses (it is a relation table and has no ID). Each user CAN have many words and each word CAN be repeated for different users (some users and words can be not associated). Each pair user-word has status (true or false) which is recorded in the status table.

Database:

  users            statuses              words
| id   |      | user_id         |      | id   |   
| name |      | word_id         |      | word |
|      |      | status: boolean |      |      |

I want to create a web page that will contain all the words that are HAS NO status for the current user.

Models:

class User < ActiveRecord::Base
  has_many :statuses
  has_many :words, through: :statuses
end

class Status < ActiveRecord::Base
  belongs_to :user
  belongs_to :word
  validates :word, uniqueness: {scope: :user}
end

class Word < ApplicationRecord
  has_and_belongs_to_many :users
end

I wrote a request it works fine but it is ugly and I feel that is not right

def words_without_status
    sql = "SELECT words.* FROM words
           LEFT  JOIN (SELECT status.* FROM status
           LEFT  JOIN users ON users.id = status.user_id
           WHERE (status.user_id = #{current_user.id})) AS tmp
           ON words.id = tmp.word_id
           WHERE tmp.word_id IS NULL
           ORDER BY id"
    @words = ActiveRecord::Base.connection.execute(sql)
end

Here's the code that returns all the words with status for a current_user but I need the opposite.

@words = current_user.words

Thank you.

Answer 1

Disclaimer: You do not actually use your status.status boolean column anyhow. Assuming that is simply an unnecessary information (so that "word that HAS NO status" means there is actually no record in status table), your initial sql seems fine, but may be we could work out something more elegant.

Pretend you're trying to get the IDs of words which have any status for a particular user first:

SELECT status.word_id FROM status WHERE (status.user_id = #{current_user.id})

Getting all the words without a status is an opposite operation, so you could do it with:

SELECT words.* WHERE words.id NOT IN (
  SELECT status.word_id FROM status WHERE (status.user_id = #{current_user.id})
)

In case you prefer an activerecordish approach, it could be written as:

@words = Word.where("id NOT IN (SELECT status.word_id FROM status WHERE (status.user_id = :user_id))", user_id: current_user.id)

Using LEFT JOINS instead of NOT IN could be another option, but I ommit this one for you to discover. Actual perfomance difference should be measured for each particular situation separately.