How to create/update multiple records with less database transactions (avoiding loop).?

Question

For creating I used

contact_ids = params[:ticket_note][:contact_ids].reject(&:blank?)
contact_ids.each do |contact_id|
  @ticket_note_recipient = TicketNoteRecipient.new
  @ticket_note_recipient.ticket_note_id = @ticket_note.id
  @ticket_note_recipient.account_contact_id = contact_id
  @ticket_note_recipient.save
end

For Updating I used

contact_ids = params[:ticket_note][:contact_ids].reject(&:blank?)
  @d_ticket_note_recipients = TicketNoteRecipient.where(:ticket_note_id =>@ticket_note)
  .where.not(:account_contact_id => contact_ids).delete_all

  contact_ids.each do |contact_id|
    @ticket_note_recipient = TicketNoteRecipient.where(:ticket_note_id =>@ticket_note)
    .where.not(:account_contact_id => contact_id).first
    if @ticket_note_recipient.blank?
      @ticket_note_recipient = TicketNoteRecipient.new
      @ticket_note_recipient.ticket_note_id = @ticket_note.id
      @ticket_note_recipient.account_contact_id = contact_id
      @ticket_note_recipient.save
    end
  end

How can we do this with fewer database transactions?

https://stackoverflow.com/questions/15784305/batch-insertion-in-rails-3#answer-15806834 talks about an activerecord-import gem that could result in a single insert statement — Int'l Man Of Coding Mystery, Jul 09 '19 at 08:07

score 1 · Answer 1 · answered Jul 04 '19 at 03:30

It is not possible to insert/modify multiple records with rails out of the box. However you can use activerecord-import gem to do this.

Rails does take arrays by default when creating new objects, like so:

VoteRecord.create(
  [
    { :prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]) },
    { :prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81]) }
    ...
  ]
)

However this would still create n queries, one per insertion, so the only way to achieve this (without writing a SQL query on your own) is by using activerecord-import

score 0 · Accepted Answer · answered Jul 04 '19 at 11:55

If you're really talking about fewer transactions as stated in your question then this is the way to go, wrapping it in a Transaction

ApplicationRecord.transaction do
  contact_ids.each do |contact_id|
     # ...
     @ticket_note_recipient.save
   end
 end
end

How to create/update multiple records with less database transactions (avoiding loop).?

2 Answers2