Create if record does not exist

Question

I have 3 models in my rails app

class Contact < ActiveRecord::Base
  belongs_to :survey, counter_cache: :contact_count
  belongs_to :voter
  has_many :contact_attempts
end

class Survey < ActiveRecord::Base
  has_many :questions
  has_many :contacts
end

class Voter < ActiveRecord::Base
  has_many :contacts
end

the Contact consists of the voter_id and a survey_id. The Logic of my app is that a there can only be one contact for a voter in any given survey.

right now I am using the following code to enforce this logic. I query the contacts table for records matching the given voter_id and survey_id. if does not exist then it is created. otherwise it does nothing.

if !Contact.exists?(:survey_id => survey, :voter_id => voter)
   c = Contact.new
   c.survey_id = survey
   c.voter_id = voter
   c.save
end

Obviously this requires a select and a insert query to create 1 potential contact. When I am adding potentially thousands of contacts at once.

Right now I'm using Resque to allow this run in the background and away from the ui thread. What can I do to speed this up, and make it more efficient?

If you're adding thousands at once why not do it with a bulk insert or some other mechanism outside of AR? — Dave Newton, Feb 12 '14 at 04:13

score 50 · Answer 1 · edited Apr 07 '18 at 13:52

50

You can do the following:

Contact.where(survey_id: survey,voter_id: voter).first_or_create

edited Apr 07 '18 at 13:52

Box Box Box Box

5,094
10
49
67

answered Jan 14 '15 at 12:52

bazinga012

713
1
5
10

carpamon · Accepted Answer · 2014-02-12T04:23:08.283

You should add first a database index to force this condition at the lowest level as possible:

add_index :contacts, [:voter_id, :survey_id], unique: true

Then you should add an uniqueness validation at an ActiveRecord level:

validates_uniqueness_of :voter_id, scope: [:survey_id]

Then contact.save will return false if a contact exists for a specified voter and survey.

UPDATE: If you create the index, then the uniqueness validation will run pretty fast.

score 20 · Answer 3 · answered Feb 12 '14 at 04:10

20

See if those links can help you.

Those links are for rails 4.0.2, but you can change in the api docks

From the apidock: first_or_create, find_or_create_by
From the Rails Guide: find-or-create-by

answered Feb 12 '14 at 04:10

Cassio Cabral

2,652
3
23
37

score 4 · Answer 4 · answered Feb 12 '14 at 04:16

It would be better if you let MySQL to handle it.

Create a migration and add a composite unique key to survey_id, voter_id

add_index :contact, [:survey_id, :voter_id], :unique=> true

Now

Contact.create(:survey_id=>survey, :voter_id=>voter_id)

Will create new record only if there is no duplicates.

Create if record does not exist

4 Answers4