Level 18
How're you currently adding one row?
Feb 18, 2016
16
Level 2
How best to copy a row of data from one table to another?
I have a need to copy a row of data from one database table to another.
What is the best means by which to accomplish this in Laravel?
The question at http://stackoverflow.com/questions/25043944/copying-one-rows-data-to-another-row-with-laravels-eloquent-or-fluent is very similar, but the accepted answer seems to assume that some version of the record already exists in both tables (whereas in my case, the record exists only in the first table).
I found the /Illuminate/Database/Eloquent/Model::setTable() method, and it seems to "work" in that when I inspect the model after calling it, the #table property reflects the new value, but when I call save() on the model, the data is not written to the DB. Yet, the save() call returns true.
Further, I notice that if I pass an invalid/non-existent table name, e.g., setTable('table_does_not_exist'), the call still returns true.
Any assistance in this regard would be much appreciated!
Thanks in advance!
Level 2
@JoeDawson Hello, and thanks for taking a look!
I'm not sure I understand your question. Are you asking how I am adding the row to be copied to the source table in the first place?
To explain a bit more, I have two tables: users and users_deleted. When a user is deleted, I need to copy the user record from users to users_deleted. (I'm aware of Eloquent's soft-delete capability, but it's not appropriate for this specific situation.)
Currently, the tables share identical structures, but that may not be the case in the future. It is possible that users_deleted may contain a different number of columns, in which case the model-driven approach may not be viable longer-term (unless it's possible to add/remove properties [columns] on the model dynamically, before calling save() against it).
I'm not opposed to doing this in two steps, e.g., select the data and then insert it with Query Builder. I'm just trying to understand what options are available before settling on an approach.
Thanks again! Happy to answer any other questions (and please do let me know if I didn't answer your previous question adequately).
Level 2
Hi I was also not able to find a simple solution for this. I came up with the following:
$order_id = $request['survey_id'];
$order_items = OrderItems::where('order_id', '=', $order_id )->get()->toArray();
foreach ($order_items as $item)
{
//$item['id'] = null; (optional)
SurveyPaidItems::insert($item);
}
// we convert $order_items to an array in order to use the insert method that expects an array, not an object. // $item['id'] will allow you to enter duplicates, else you will get an error that a record already exist with the key if you run it more than once for the same item.
Daniel Farina
Level 2
so the question is, is there a better way to do it with eloquent?
Level 6
I think this tricks is best.
$tasks = Tasks::find(1); $newTask = $tasks->replicate(); $newTask->save(); source: http://laraveldaily.com/quick-replication-of-model-row/
1 like
Level 2
@mehedi101 But won't that work only when duplicating rows in the same table?
I want to retrieve a model and save it to a different table.
So far, the only way I've found to do this is (as @chileno suggested) by retrieving the model instance and passing it into Query Builder via DB::table('users_deleted')->insert($user->toArray()) or into Eloquent using Mass Assignment, via DeletedUser::create($user->toArray()).
To be clear, neither of these is a "bad approach". I can live with either one. :)
Level 67
You haven't figured this out in the 2 years since you posted it?
I'd just use a raw query and not even bother retrieving with eloquent.
insert into destination_table (col1, col2, col3) select col1, col2, col3 from source_table where id = 2 or something. It does the select and insert in a single statement without involving laravel at all.
1 like
Level 2
@Cronix I "figured it out" before I started the thread. I wanted to know if there is a more "appropriate" method with respect to my specific needs, similar to replicate(), but that works across tables. Apparently, there wasn't then, and there isn't now.
Sure, a raw SQL query might be more "efficient", but it lacks the benefits of a DBAL (which we get with Query Builder), and the benefits of Events (which we get with Eloquent).
I know I'm boring you here, but to illustrate to other less knowledgeable users the reasons for which Query Builder or Eloquent might be preferable in certain scenarios as compared to raw SQL:
- What happens if my database's query syntax changes over time? I'd have to update any affected raw queries. Had I used Query Builder or Eloquent, a single Laravel update would bring my queries into compliance.
- What if I rename my table? A real-world example might be moving the table into a different schema. Then I'd have to hunt-down every raw query that uses the literal table name and update it.
- What happens if I add a new column to my
userstable (and mydeleted_userstable)? I'd have to hunt-down and edit any raw queries, whereas had I used a model-driven approach, I wouldn't have to do anything.
I'm sure there are other drawbacks to using raw SQL for this.
Regarding the Eloquent approach vs raw SQL, the lack of support for Events is self-explanatory.
Tangentially, what's the rub here? That I commented on a two-year-old thread? Or that I haven't chosen an Answer?
If it's resurrecting an old thread, the passage of time doesn't make something any less relevant, especially given that there still isn't a move() or clone() method (or whatever name makes sense) for copying or cloning a model from one table to another.
Closing or otherwise discouraging posts in "old threads" is one of the most annoying practices on the Internet. It forces users to duplicate topics that have been discussed at length, often with valuable (and still relevant) contributions that are sidelined when the existing thread is closed. And that's to say nothing of automatic notifications that contributors to the "old" thread may have received had the new/duplicate thread not been created.
If "resurrecting" bothers people, then Jeff should add a "Don't bump thread with my reply" checkbox.
@jlrdw Thanks for the useful contribution... that's exactly what I intend to do. :) I'll keep you posted.
Level 2
Eloquent and events are two different things.
I know that. My point was that using Eloquent to "copy" the row from one table to another provides the ability to take advantage of Eloquent-specific events.
Suppose I need to perform some other arbitrary task any time I copy a row in this manner. That functionality would come standard if I used Eloquent, whereas I would have to build it manually if I use a raw query (or Query Builder).
Then eloquent wouldn't work since eloquent converts to normal sql at runtime. It's a shortcut language.
Again, I know this. My point was that Laravel's SQL grammar implementation will evolve over time to accommodate changes in database-specific SQL syntax. It provides a level of abstraction that prevents me from having to worry about quoting style or other database-specific nuances that may evolve over time.
Speaking of quoting syntax, what if I later need to switch from MySQL to PostgreSQL (or any other DB)? That's another good reason not to use raw SQL. I'm guessing you've not used PostgreSQL in your projects, or are never likely to switch to it, because quoting style is one of the most significant departures from MySQL, and presents a real problem for raw queries.
In any case, I'll mark my own reply as Accepted, because, clearly, there's no "better" method for this than those already discussed. Why Taylor saw it fit to include a replicate() method but not the cross-table equivalent is anyone's guess.
Level 1
@mehedi101 has a great answer, but let me expand upon that by using setTable():
$oldTask = Task::find(1);
$newTask = $oldTask->replicate();
$newTask->setTable('name_of_new_table');
$newTask->save();
When chained in one line:
$newTask = Task::find(1)->replicate()->setTable('name_of_new_table')->save();
If you need to specify the name of the original table too:
$newTask = (new Task)
->setTable('name_of_old_table')
->find(1)
->replicate()
->setTable('name_of_new_table')
->save();
You can even use setConnection if your new table is in a different database. This is where Eloquent would be extremely useful in your example when converting from mysql to postgresql:
$newTask = (new Task)
->setConnection('mysql')
->setTable('name_of_old_table')
->find(1)
->replicate()
->setConnection('postgresql')
->setTable('name_of_new_table')
->save();
Finally, if you need to delete the original row, you can wrap both query completions in a DB transaction to ensure they both succeed or fail together.
$oldTask = (new Task)
->setConnection('mysql')
->setTable('name_of_old_table')
->find(1);
$newTask = $oldTask
->replicate()
->setConnection('postgresql')
->setTable('name_of_new_table');
DB::transaction(function() {
$newTask->save();
$oldTask->delete();
});
I don't think the Laravel contributors meant to exclude a cross-table equivalent of replicate() as you suggest. I think they just give devs a lot of handy Eloquent functions that can be easily changed together in any order. Its as simple as ->replicate()->setTable().
If they start making lots of combinatory functions like ->replicateAndSetTable() they would probably have a lot more code to maintain, without much benefit. I agree that they could add this one use case to the docs and that would be helfpul.
5 likes
Level 7
Great
Level 1
@famouspotatoes Nice
Level 1
Thanks @famouspotatoes ! Bear in mind that save() (at least as of 6.x) returns true or false, not the model! So if you need the model, do replicate(), and then the save as a separate step.
1 like
Level 4
@chuck_wood That's true, idk if you are aware, but Laravel has a built in helper function called tap, which returns the actual object, variable or reference that you pass to it. So:
//This returns True or False
$object->save();
//This returns the actual object
tap($object)->save();
I think is pretty cool and it has a lot of usages
Level 5
Hi,
I faced the same situation, I solve it this way, first create a model of the table you want to copy to... as we are doing a mass assignment (security concerns please read more about it if you are curious) we need to put:
protected $guarded = [];
inside the class as like an attribute this will allow the mass assignment. Then in your code just simply do the assignment like this
$history = EmployeeHistory::create($employee->toArray());
where $history is the new record, EmployeeHistory is the new model where we want to copy to, $employee is the record we want to copy from, we need to convert our object that we are copying from to array with the toArray() method, and that's it. If you want to do more modifications to the copy you can do that with the object copy in this case
$history->attribute = 'new value';
$history->save()
Level 2
@famouspotatoes & @cbj4074 I used famouspotatoes' example and put it in an updating event in the booted method of the parent model. I cloned the parent model's migration and added a parent_id for my use case. It works really well!
/**
* The "booted" method of the model.
*
* @return void
*/
protected static function booted()
{
static::updating(function ($model) {
$newModel = $model->replicate();
$newModel->setTable('name_of_new_table');
$newModel->parent_id = $model->id; // optional
$newModel->save();
});
}
Please or to participate in this conversation.