More Efficient Find Or Create Multiple Records In Rails

Question

I have an app that needs to send out user invitations for events. When a user invites a friend (user) to an event, a new record connecting the user to the event is created if one doesn't already exist. My models consists of user, event, and events_user.

class Event
    def invite(user_id, *args)
        user_id.each do |u|
            e = EventsUser.find_or_create_by_event_id_and_user_id(self.id, u)
            e.save!
        end
    end
end

Usage

Event.first.invite([1,2,3])

I don't think the above is the most efficient way to accomplish my task. I envisioned a method like

 Model.find_or_create_all_by_event_id_and_user_id

but one does not exist.

Models without validations

class User 
  has_many :events_users 
  has_many :events 
end 
class EventsUser 
  belongs_to :events 
  belongs_to :users 
end 
class Event 
  has_many :events_users 
  has_many :users, :through => :events_users 
end

Answer 1

It might be quicker to first fetch all existing records and then create all missing records:

class Event
  def invite(user_ids, *args)
    existing_user_ids = event_users.where(user_id: user_ids).pluck(:user_id)
    (user_ids - existing_user_ids).each do |u|
      event_users.create(user_id: u)
     end
  end
end

This way you make only 1 query if all event_users already exist. However, if no event_users exist, this method does an extra query - compared to the number of queries required for each single EventUser creation.

Answer 2

What do you mean by most efficient? I will assume that by efficient you mean performant, and not elegant, DRY, maintainable code, etc.

From a DB viewpoint, if you want to insert 100 records into the DB, this will translate to 100 "INSERT INTO events_models VALUES (x, x)" sql queries (and maybe 100 "SELECT COUNT(*) .." queries if you also have a uniqueness validation). So, even if the method you want would be implemented in AR, it would still have a loop on the attribute arrays with a save on each event_id, user_id pair).

From Ruby/Rails point of view, if you want to have validations/callbacks/etc on your model, then you must create an ActiveRecord instance one by one in a loop. Now, if you want to super optimize your method (to drop the instantiation of an ActiveRecord class), you can manually write the sql queries (therefore saving some time and memory). The gains are however minimal in comparison with the risks.

Btw, e.save! is not necessary as:

The same dynamic finder style can be used to create the object if it doesn’t already exist. This dynamic finder is called with find_or_create_by_ and will return the object if it already exists and otherwise creates it, then returns it.

Answer 3

If you don't have any validations or callbacks in the join model, the fastest method is raw sql:

self.connection.execute(%Q{insert into events_users (event_id,user_id) 
                             select distinct events.id,users.id from events,users 
                             where events.id = #{self.id} 
                             and users.id in ( #{user_ids.join(",")} )
                           }
                        )

You could write a query to eliminate existing records from user_ids before making the above call. I had a previous question about this, specifically about join tables that don't have a model.

Since you do have a join model, you could use the ar-import gem:

books = []
10.times do |i| 
  books << Book.new(:name => "book #{i}")
end
Book.import books