CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5
Joao Sousa
Joao Sousa

Reputation: 11

Converting SQL query into Active Record

I've been trying with no success to translate the following SQL query into ActiveRecord one:

SELECT invited.id
FROM (
    SELECT users.id 
    FROM users 
    WHERE users.invited_by_id IS NOT NULL 
) AS invited
JOIN (
    SELECT id 
    FROM users 
    WHERE users.id NOT IN ( 
        SELECT user_id 
        FROM report_logs
    ) 
) AS no_report ON invited.id = no_report.id;

I've been using the following command but it doesn't return the same results as the SQL version.

@users = User.includes(:report_logs)
         .on(report_logs: { user_id: nil })
         .where("invited_by_id IS NOT NULL")

The requirement for the query is to Find all the users who have been invited by another user and do not possess a report linked to its id.

Upvotes: 0

Views: 62

Answers (1)

Sean
Sean

Reputation: 983

I think this should be handled by active record scopes. Lets break this down a tad

first, we need a scope to find users that are invited. This can be solved by this scope User.where.not(invited_by_id: nil).

The second scope we need can be constructed again with a scope. This can be solved by User.left_outer_joins(:report_logs).where( report_logs: { id: nil } )

Now we can save these as named scopes in our model for convenient use.

class User
  has_many :report_logs
  scope :invited, -> { where.not(invited_by_id: nil) }
  scope :without_report_logs, -> { left_outer_joins(:report_logs).where( report_logs: { id: nil } ) }
end

User.invited.without_report_logs

Upvotes: 1

Related Questions