Want to be able to insert multiple rows at two different values
Is it possible to create multiple rows for one record based on two different column values?
Example: in my table for my model record I have a column named full_range
, this will have dates selected between date leave_start
and leave_end
so for example I can select 05/01/15
both my leave_start
date and 05/05/15
as my date leave_end
, this will give me 05/01/15, 05/02/15, 05/03/15, 05/04/15, 05/05/15
for mine full_range
.
Then for my other column called the range_days
value will be 5
because I have 5
days between 05/01/15
and 05/05/15
.
What I would like to do is split my full_range values ββbased on range_days
, and I would like to insert multiple rows for each date from my full range, and I think it range_days
will come into play to say create the value of the rows being created.
Right now I am only getting one line.
ID Created_at Full_range_date emp_id range_days leave_start leave_end full_range
10686 1-May-15 5/1/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
So, in theory, what I would like to see in my database would be that it first looks at full_range
and grabs at the first number that fills it for full_range_date
, and then looks at the next and the next ... based on range_days
it does 5 days, which is 5 lines.
ID Created_at Full_range_date emp_id range_days leave_start leave_end full_range
10686 1-May-15 5/1/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
10687 1-May-15 5/2/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
10688 1-May-15 5/3/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
10689 1-May-15 5/4/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
10690 1-May-15 5/5/2015 TEST1 5 05/01/15 05/05/15 05/01/15 05/02/15 05/03/15 05/04/15 05/05/15
How can I do this, any help would be greatly appreciated!
I am using rails 4.1.8
also for more info here's my input controller.
class EntryController < ApplicationController
def new
@entry = Entry.new
respond_to do |format|
format.html# new.html.haml
format.xml { render :xml => @entry }
end
end
def create
params.permit!
@entry = Entry.new(params[:entry])
@entry.t_d
@entry.day_hours
@entry.current_user = current_user
respond_to do |format|
if @entry.save
if current_user.email.nil?
@entry.create_totals
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created, but you will not recieve any notifications, because you email is blank!') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
else
@entry.create_totals
EntryMailer.submit_for_approval(@entry).deliver
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created.') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
end
else
format.html { render :action => "new" }
format.xml { render :xml => @entry.errors, :status => :unprocessable_entity }
end
end
end
and my input model
class Entry < ActiveRecord::Base
self.primary_key = 'id'
end
Based on the answer I tried, but still it only created one row, what she would like to do is create a new row for each date from full_range
def create
params.permit!
@entry = Entry.new(params[:entry])
@entry.t_d
@entry.day_hours
@entry.current_user = current_user
# send my email
respond_to do |format|
begin
Entry.transaction do
@entry.full_range.split(' ').each do |date|
entry = Entry.new( @entry.attributes.to_options )
entry.full_range = date
entry.save!
end
end
if current_user.email.nil?
@entry.create_totals
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created, but you will not recieve any notifications, because you email is blank!') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
else
@entry.create_totals
EntryMailer.submit_for_approval(@entry).deliver
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created.') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
end
rescue
format.html { render :action => "new" }
format.xml { render :xml => @entry.errors, :status => :unprocessable_entity }
end
end
end
Just for a chuckle, I tried just making a raw sql expression like in my input model
so this inserted the correct number of rows, but all the data did not correspond to the exact change.
def trying_it_all
@id = self.id
@leave_end = self.leave_end.to_date
@leave_start = self.leave_start.to_date
@range_vals = self.range_days
if !(self.range_days == 0)
range_days.times do
sql = "insert into entry values('#{@id}', '#{@c_d}', '#{@c_u}', '#{@emp_id}', '#{@range_vals}', 'N')"
Entry.connection.execute(sql)
end
end
source to share
This is absolutely possible, and there are a number of things in your implementation. The main blow will be your #create action. You already have all the information you need, this is how I personally approach it.
First of all, when creating several records at once, it always uses a transaction . If you are not familiar, the idea is that your changes will only be committed to the database if each record is saved successfully (so if one of the records fails, it will rollback all the others, preventing your data from becoming inconsistent). The implementation might look something like this:
def create
params.permit!
@entry = Entry.new(params[:entry])
@entry.t_d
@entry.day_hours
@entry.current_user = current_user
respond_to do |format|
if # Condition here
ActiveRecord::Base.transaction do
# Create your records here
end
else
# Indicate failure
end
end
end
Now you can notice that there are no conditions for the if statement. This is because with multiple transactions, we need a better way to react to whether they succeed, not just from @ entry.save. The easiest way to do this is with the Begin / Rescue block (which you can recognize as Try / Catch from more common languages). It will look like this:
respond_to do |format|
begin
ActiveRecord::Base.transaction do
# Create your records here
end
rescue
# Indicate failure
end
end
How it works, the Begin block will execute and the transaction will start. Inside a transaction, if something doesn't work out, we'll make a mistake. The entire transaction will be rolled back and the error will take us out of the Begin block and instead into the Rescue block.
Now, as part of a transaction, we need to create some records. This part should be pretty simple. We'll need to use a loop to create multiple records based on range_days. Inside a transaction, we want to do something like this:
(1..@entry.range_days).each do
entry = Entry.new( @entry.attributes.to_options )
entry.full_range_date = # Calculation to determine the date of this entry
entry.save!
end
This will create one record for each day in range_for . The first line in the loop creates a variable without an instance with the same values ββas @entry. On the second line, you will change the value of full_range_date. The third line uses .save! a function that has an important difference compared to the .save function ; this will throw an error if it fails. This is a trigger that will allow you to exit the Begin block and jump to the Rescue block if something goes badly wrong.
As far as your calculations for setting a new full_range_date are concerned, this will either involve date functions or string manipulations (depending on how you handle dates). See my REVISION at the bottom of the answer for an idea on how to do this. So your create function might look something like this:
def create
params.permit!
@entry = Entry.new(params[:entry])
@entry.t_d
@entry.day_hours
@entry.current_user = current_user
respond_to do |format|
begin
ActiveRecord::Base.transaction do
(1..@entry.range_days).each do
entry = Entry.new( @entry.attributes.to_options )
entry.full_range_date = # Calculation to determine the date of this entry
entry.save!
end
end
if current_user.email.nil?
@entry.create_totals
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created, but you will not recieve any notifications, because you email is blank!') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
else
@entry.create_totals
EntryMailer.submit_for_approval(@entry).deliver
format.html { redirect_to(entry_path( @entry ), :notice => 'Entry successfully created.') }
format.xml { render :xml => @entry, :status => :created, :location => @entry }
end
rescue
format.html { render :action => "new" }
format.xml { render :xml => @entry.errors, :status => :unprocessable_entity }
end
end
end
Closing comment
params.permit! this is an incredibly tempting method to use as it prevents you from worrying about strong parameters or having to update your controller when your model changes ... but it's incredibly dangerous. Not only can a user pass you fields that you don't expect (thus forcing you to handle volumes of data for which you are not prepared), it also allows the hidden field to be user-savvy. For example, if I POST to your post, I could specify {: Created_At => 1-Jan-2015}, making it look like I created this post four months before, and devalue that column completely. In this case, this is not important, but imagine that you had a User model with a field: is_administrator. Then everyone can create users with administrator rights.It might be helpful to read if you're interested.
REVISED
Quick add! If you already have Full_range_date values ββin full_range variable, you can replace the original loop like this:
@entry.full_range.split(' ').each do |date|
entry = Entry.new( @entry.attributes.to_options )
entry.full_range_date = date
entry.save!
end
This will turn the full_range value into an array, enumerate each element, and set the full_range_date value for you without further computation.
source to share