Getting the correct users with activerecord

Question

Currently I have a table of 10,000 users who I want to search. I also have another table of 3 users that I have approved. I want to make it so that the search result only returns 9997 of the users. In other words, if I have approved a user, I do not want them to appear in the search result. Here is what I have so far for the code:

approved_users = Approval.where(user_id: logged_in_user.id) 
approved_user_ids = approved_users.map { |user| user.approved_user_id}
pre_search_results = User.find_by_sql("SELECT *
  FROM users LEFT JOIN approvals
  ON users.id = approvals.approved_user_id
  WHERE name LIKE ? OR operator_id LIKE ?"
  )

The first statement approved_users returns this when I manually added 3 users:

id: 10, user_id: 39, approved_user_id: 37
id: 11, user_id: 39, approved_user_id: 35>,
id: 12, user_id: 39, approved_user_id: 41>

I then made approved_user_ids just give me the id portion of the above statement, so it returns this:

[37, 35, 41]

Now, I want pre_search_results to return me the 9997 other users and I have the above statement. It is currently returning nothing. Any ideas what can be done?

score 0 · Answer 1 · answered Oct 27 '15 at 20:00

0

To get all users whose ids are not present in approved_user_ids, you can use: pre_search_results = User.where("id NOT IN (?)", approved_user_ids)

answered Oct 27 '15 at 20:00

elements

1,047
2
9
21

Is there any way I can combine that with: pre_search_results = User.where('name LIKE ? OR operator_id LIKE ?', "%#{query}%", "%#{query}%").order(:name) – lakeIn231 Oct 27 '15 at 20:09
@JohnSmith I'm a bit confused... your question says you want "`pre_search_results` to return me the 9997 other users", but I'm guessing you want to query THOSE results with the query in your comment? I'll edit my answer. – elements Oct 27 '15 at 20:24
you are correct! I apologize, should have been more clear. – lakeIn231 Oct 27 '15 at 20:37

score 0 · Answer 2 · edited May 23 '17 at 12:31

My thought is to split your two different conditions into two different scopes, which can then be chained together...

I'm also assuming a relationship in your User model.

user.rb

class User
  has_many :approvals

  scope :has_been_approved, -> { joins(:approvals) }
  scope :has_not_been_approved, -> ( includes(:approval).where(approvals: { user_id: nil })
  scope :search_by_name, ->(term) { where("name LIKE :t", t: term) }

...which lets you do something in your controller (or wherever) like...

User.has_not_been_approved.search_by_name("Rodolfo")

The scope for has_not_been_approved is a little awkward. Here's where I found the example: Rails find record with zero has many records associated

Getting the correct users with activerecord

2 Answers2