Rails model/query for "profiles of people from contact list who are attending event"

Question

So there are a lot of relationships involved in this. The end goal is to show on the event view people from the current_user's contact list who are attending event. The current model layout is as such:

# Table name: profiles
#  id                  :integer          not null, primary key
#  profileable_id      :integer
#  profileable_type    :string(255)
class Profile < ActiveRecord::Base
  belongs_to :profileable, polymorphic: true
  has_many :events, as: :eventable, dependent: :destroy
end

# Table name: events
#  id             :integer          not null, primary key
#  eventable_id   :integer
#  eventable_type :string(255)
class Event < ActiveRecord::Base
  belongs_to :eventable, polymorphic: true
  has_many :attendants, dependent: :destroy
end

# Table name: attendants
#  id                    :integer          not null, primary key
#  referrer_profile_id   :integer
#  event_id              :integer
#  responding_profile_id :integer
class Attendant < ActiveRecord::Base
  belongs_to :event
end

# Table name: contacts
#  id         :integer          not null, primary key
#  user_id    :integer
class Contact < ActiveRecord::Base
  belongs_to :user
  has_one :profile, as: :profileable, dependent: :destroy
end

Clearly some more relationship related lines would help, some scopes, and something to help with a join (I believe).

The kind of thing I'm looking to do will be something like:

@attendants = Attendant.where(event_id: @event.id)
@contacts = Contacts.where(user_id: current_user.id)
@result = @attendents.select {|i| @contacts.any? {|c| i.responding_profile_id == c.profile.id } }

And as I understand it this is the least efficient way to do this. If you can help me with the proper joins, merge, scope, and additional has/belongs relationship lines to complete this I will be forever grateful!

---

Here's the relationship model diagram. Profile and Event are polymorphic. Attendant is a relation table.

I've queried an example of Attendant and Profile as JSON. Maybe this will help.

Contact.find(8).profile.as_json
  Contact Load (0.6ms)  SELECT  "contacts".* FROM "contacts"  WHERE "contacts"."id" = $1 LIMIT 1  [["id", 8]]
  Profile Load (0.3ms)  SELECT  "profiles".* FROM "profiles"  WHERE "profiles"."profileable_id" = $1 AND "profiles"."profileable_type" = $2 LIMIT 1  [["profileable_id", 8], ["profileable_type", "Contact"]]
 => {"id"=>11, "first_name"=>"Apple", "last_name"=>"Dumpling", "profileable_id"=>8, "profileable_type"=>"Contact", "created_at"=>Fri, 10 Oct 2014 02:21:20 UTC +00:00, "updated_at"=>Fri, 10 Oct 2014 02:21:20 UTC +00:00} 

Attendant.second.as_json
  Attendant Load (0.8ms)  SELECT  "attendants".* FROM "attendants"   ORDER BY "attendants"."id" ASC LIMIT 1 OFFSET 1
 => {"id"=>2, "confirmation"=>"attending", "referrer_profile_id"=>1, "responding_profile_id"=>8, "event_id"=>1, "created_at"=>Fri, 10 Oct 2014 17:46:44 UTC +00:00, "updated_at"=>Fri, 10 Oct 2014 17:46:44 UTC +00:00} 

Answer 1

The problem with this is the profile being polymorphic means it chooses it's parents by it's association table. In my question I had asked for finding the same profile for two different parent items by using the same ID/Type. This is impossible as it is not the same. The current code does NOT work. To fix this solution I added a field to Contact of original_profile_id which gets written upon duplication of person's profile to always have the reference to the original source. Now suddenly what was impossibly complicated because it was impossible, is easily remedied.

Answer 2

I'm not sure if this is right but looking at your schema here is what I came up with:

Profile.select(Arel.star).where(
  Profile.arel_table[:profileable_id].in(
    Event.select(Attendant.arel_table[:responding_profile_id]).where(
      Event.arel_table[:id].eq(@event.id).and(
        Attendant.arel_table[:responding_profile_id].in(
         Attendant.select(Attendant.arel_table[:responding_profile_id]).where(
            Contact.arel_table[:user_id].eq(current_user_id)
          ).joins(
            Attendant.arel_table.join(Contact.arel_table).on(
              Attendant.arel_table[:responding_profile_id].eq(Contact.arel_table[:id])
            ).join_sources
          ).ast
        )
      )
    ).ast
  )
)

This is the SQL for reference:

SELECT * from Profile 
    where Profile.profileable_id in 
        (select Attendant.responding_profile_id from Event 
            where Event.id = @event.id
            and Attendant.responding_profile_id in
                (select Attendant.responding_profile_id from Attendant inner join Contact 
                    on Attendant.responding_profile_id = Contact.id
                    where Contact.user_id = current_user_id
                )
        )

Answer 3

I can't test it without your DB, but looking at your schema, this should return the profiles of every contact of the current user that is attending the given event.

 Profile.where('id 
   IN (SELECT attendants.responding_profile_id FROM attendants WHERE attendants.responding_profile_id 
   IN (SELECT profiles.id FROM profiles WHERE profiles.id 
   IN(SELECT contacts.profile_id FROM contacts WHERE contacts.user_id = ?)) 
   AND attendants.event_id = ?)', current_user.id, @event.id)