I am using roo-gem to import the following spreadsheet of authors designed as below:
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