Subtract two ActiveRecord::Relation objects

Question

I would like to give users of my application the opportunity to remove from their microposts' feed unwanted microposts. By default the microposts' feed is made of the user's own microposts plus the microposts from followed users:

def feed
  following_ids = "SELECT followed_id FROM relationships
                   WHERE  follower_id = :user_id"
  Micropost.where("user_id IN (#{following_ids})
                   OR user_id = :user_id", user_id: id)
end

I created a Quarantine model where users and unwanted microposts are associated. Then I looked for an ActiveRecord::Relation method that allowed me to subtract from the above where the following where:

microposts_ids = "SELECT micropost_id FROM quarantines
                  WHERE  user_id = :user_id"

Micropost.where("micropost_id IN (#{microposts_ids})", user_id: id)

I could not find any method that correspond to the - arrays operator. However I found method merge in a Stackoverflow question: Combine two ActiveRecord::Relation objects, that, as far as I understood, would allow me to chain the wheres as follows:

Micropost.where("user_id IN (#{following_ids}) OR user_id = :user_id", user_id: id).merge(Micropost.where.not("micropost_id IN (#{microposts_ids})", user_id: id))

The difference is that I changed the second where into a where.not.
The problem with this solution is that the where.not would load all Microposts that are not quarantined, which is a heavier job for the database than loading only the quarantined microposts. Is there any alternative solution to the merge method, such as a method that subtracts from the original feed the quarantined microposts?

score 3 · Accepted Answer · answered Jun 29 '17 at 12:51

3

For a particular user

microsposts_not_to_show = Micropost.joins(:quarantines).where("quarantines.user_id" => user.id)
all_microposts = Micropost.where("user_id" => user.id) + Micropost.joins(:user => : relationships).where("relationships.follower_id" => user.id)
microposts_to_show = all_microposts - microposts_not_to_show

answered Jun 29 '17 at 12:51

moyinho20

624
6
13

I am not sure the definition of `microposts_not_to_show` is correct: it should return a Micropost object, not a Quarantine object: the where argument contains a Quarantine object. – Asarluhi Jun 29 '17 at 13:33
We are joining the tables. It will not show a Quarantine object until explicitly specified using the select method. – moyinho20 Jun 29 '17 at 13:37
I receive the following warning: `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column quarantines.user_id does not exist`. – Asarluhi Jun 29 '17 at 13:40
Could you tell me what are the columns in the quarantines table? – moyinho20 Jun 29 '17 at 13:42
`user_id` and `micropost_id`. However `Micropost.joins(:quarantines)` is a Micropost object, not a Quarantine object, that's why Active Record complains: a Micropost object does not have a `user_id` column: `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column microposts.user_id does not exist`. – Asarluhi Jun 29 '17 at 13:48
What is exactly the error? `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column quarantines.user_id does not exist` OR `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column microposts.user_id does not exist` – moyinho20 Jun 29 '17 at 13:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147942/discussion-between-asarluhi-and-moyinho20). – Asarluhi Jun 29 '17 at 13:52

score 0 · Answer 2 · answered Jun 29 '17 at 13:26

For Rails 5 it might look like:

class User < ApplicationRecord
  has_many :relationships
  # ...
  def feed
    Micropost
      .where(user_id: relationships.select(:followed_id))
      .or(Micropost.where(user_id: id))
      .where.not(id: Quarantine.select(:micropost_id).where(user_id: id))
  end
end

The feed returns relation which produces ony one DB request and can be chained with additional filtering, ordering - whatever you need.

Subtract two ActiveRecord::Relation objects

2 Answers2