CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5rails-activerecord
D-Nice
D-Nice

Reputation: 4870

Rails 5 Active Record - Query that determines if sibling record exists

The software I'm building helps plan events and contact people to attend events. We want to identify all Users who we have not yet invited to a specific event via a PhoneCall. To do this we want to query:

1) All Users for whom no ContactAttempt for any event exists

2) Users who for whom no ContactAttempt with event_id = this event.id and this attempted_type = PhoneCall. (this captures those who we contacted via a Text but not a PhoneCall)

User.includes(:contact_attempts).references(:contact_attempts)
.where('contact_attempts.id IS NULL OR (contact_attempts.event_id = ? AND contact_attempts.attempted_type != ?)', @event.id, "PhoneCall")

But this query leaves out third group of Users I want to target, who I have been unable to include in the query:

The Users who have a ContactAttempt for a different event_id, but not for this event_id. At first I thought it could be done with contact_attempts.event_id != ?, but that included all Users who had an existing ContactAttempt even if they have been contacted for this event_id,

How can I construct the query to also target all Users who have no ContactAttempt for this particular event with attempted_type = PhoneCall, but may have a ContactAttempt for a previous event?

Is this something that requires Arel?

Upvotes: 2

Views: 155

Answers (1)

Int'l Man Of Coding Mystery
Int'l Man Of Coding Mystery

Reputation: 1722

User.joins('LEFT OUTER JOIN contact_attempts 
            ON contact_attempts.user_id = users.id')
    .where('contact_attempts.id IS NULL 
            OR (contact_attempts.event_id = ?
            AND contact_attempts.attempted_type != ?)',
            @event.id, "PhoneCall")

I think the issue with the includes and references is it won't get users without the association, where as left join will return users both with and without contact attempts

Upvotes: 1

Related Questions