Converting SQL query into Active Record

Question

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.

Sean · Accepted Answer · 2017-09-29 21:22:17Z

1

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

answered Sep 29, 2017 at 21:22

Sean

9835 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting SQL query into Active Record

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related