Reject items not in association table instead of creating new records when importing CSV using Roo gem

90 Views Asked by At

I am using roo-gem to import the following spreadsheet of authors designed as below:

enter image description here I have tables in the database called authors, states, publishers and genres which the data in the respective excel columns map to. As you can see, the first row of headers is very detailed because the users uploading the data into the authors table are not very savvy. Because such long header names cannot map into the association columns in the table, I included a second row in the excel template and then added methods in the Author model as shown below:

class Author < ApplicationRecord
  belongs_to :state
  belongs_to :genre
  belongs_to :publisher

#validations

validates :name,  presence: true
validates :national_id,  presence: true

  def to_s
    name
  end

  def home_state
    state.try(:name)
  end

  def home_state=(name)
    self.state = State.where(:name => name).first_or_create
  end

   def publisher_name
    publisher.try(:name)
  end

  def publisher_name=(name)
    self.publisher = Publisher.where(:name => name).first
  end

  def listed_genre
    genre.try(:name)
  end

  def listed_genre=(name)
    self.genre = Genre.where(:name => name).first
  end

end

Here is authors_import.rb:

class AuthorsImport
  include ActiveModel::Model
  require 'roo'

  attr_accessor :file

  def initialize(attributes={})
    attributes.each { |name, value| send("#{name}=", value) }
  end

  def persisted?
    false
  end

  def open_spreadsheet
    case File.extname(file.original_filename)
    when ".csv" then Csv.new(file.path, nil, :ignore)
    when ".xls" then Roo::Excel.new(file.path, nil, :ignore)
    when ".xlsx" then Roo::Excelx.new(file.path)
    else raise "Unknown file type: #{file.original_filename}"
    end
  end

  def load_imported_authors
    spreadsheet = open_spreadsheet
    spreadsheet.default_sheet = 'Worksheet'
    header = spreadsheet.row(2)
    #header = header[0..25]
    (3..spreadsheet.last_row).map do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      #author = Author.find_by_national_id(row["national_id"]) || Author.new
      author = Author.find_by(national_id: row["national_id"], state_id: row["home_state"]) || Author.new
      author.attributes = row.to_hash
      author
    end
  end

  def imported_authors
    @imported_authors ||= load_imported_authors
  end

  def save
    if imported_authors.map(&:valid?).all?
      imported_authors.each(&:save!)
      true
    else
      imported_authors.each_with_index do |author, index|
        author.errors.full_messages.each do |msg|
          errors.add :base, "Row #{index + 3}: #{msg}"
        end
      end
      false
    end
  end

end

Here is my problem: when the users add any publisher or genre in the excel template that's not found in the respective publishers or genres table, that entry will be added into the tables, which I don't want. If list all the publishers or genres from the app, I find several entries which should not be there that have been created during import. The excel template has dropdowns of publishers and genres just as they appear in the corresponding tables in the database. One solution would be to lock the template, but that's not possible in the circumstances for now.

I thought first_or_create is the only method that will add a new record in case it is not found, but it seems even the first method is behaving the same here.

Is there some model validation or tweak in the method that will prevent unauthorized publishers and genres from being created?

Thanks

0

There are 0 best solutions below