Build a Livewire CSV Importer
This episode is for members only

Sign up to access "Build a Livewire CSV Importer" right now.

Get started
Already a member? Sign in to continue
Playing
18. Upserting records

Episodes

0%
Your progress
  • Total: 2h 20m
  • Played: 0m
  • Remaining: 2h 20m
Join or sign in to track your progress

Transcript

00:00
Okay, so now we're actually going to go ahead and fill in the import CSV job to actually
00:05
upsert these records into the database. So let's go ahead and first of all think about the kind of data we're going to need to pass through to import CSV. Now if we just head back over to our database, we know we've got these imports that are being
00:20
created. We're going to need to update them within the jobs to tell us how many records have been successfully inserted based on this particular import. So we're going to need to pass the import in first of all.
00:32
I'm just going to rough these out and then we'll fill them in. The next thing that we're going to need obviously is the actual model that we're working with because we need to know this generic import CSV job needs to know what model we are working with.
00:47
So we need to pass that in as well. We also need to pass in the chunk itself. So that's going to be the actual records that need to be passed in. And really importantly, let's not forget, we need to be able to pass in, we just go
01:00
ahead and upload a file, the columns that we actually want to insert. We don't just want to insert all of these bits of data from this particular CSV row. We want only the ones that we're allowing. So they're going to be the columns.
01:16
So let's start filling these in. Now we've already created the import. Let's just go and grab this and pop it up here because that's going to need to come first.
01:25
And we're going to need to assign that. So remember, that's going to give us an import model back. And we can go ahead and bring this into scope of this closure and then go ahead and pass that in.
01:34
So that one is relatively straightforward. The model itself can just be a string. We don't necessarily need to pass any kind of query builder through to this because we can grab that very easily.
01:44
So we're just going to go ahead and grab this from the model that we have stored on this particular component. The chunk, again, it's very simple because we have it through each iteration here. And the columns are just going to be the columns that we've chosen to map.
01:57
So again, this columns to map. And that is it. So let's grab these and accept these into our import CSV. So we'll do this, obviously, in the constructor.
02:09
And let's go ahead and just set the visibility of these and the types as well. So we want to type in that as the import model. The model here is just going to be a string. So let's say public string.
02:21
The chunk here is going to be an array. So let's set this to an array. And the columns to map, again, is an array. Public array.
02:31
Columns to map. And of course, we'll get rid of the reference to this because that doesn't make any sense. And we are good. So we're just using constructor property promotion here rather than passing these in and then
02:43
assigning them here. So that just depends on the PHP version that you're using. Okay. So we've got these passed in.
02:49
Now we can start to handle this and actually process the job. Now, it really depends at this point how you want these records inserted. I'm going to go ahead and upsert them, which means I'm going to go ahead and check for the uniqueness of the ID and not insert these if the ID matches.
03:06
Because if we import some data and we set an ID to 100, for example, that record already exists in the database, then we don't want to insert it. We want to kind of ignore it. We're assuming these are all going to be fresh records.
03:20
So we're going to use the ID as a kind of unique identifier. You could use any other column as a unique identifier if you wanted to. It could be the email address. It really depends.
03:28
So feel free to change around anything we do inside of this job to insert the data however you need it inserted. Okay. So how do we do this then?
03:37
Well, we're going to go ahead and say this model, that's a string, remember, but we can still use the scope resolution operator to access any of Eloquent's methods like upsert in here, if I can actually spell that correctly. So the three things that we need to pass through to here is the entire chunk.
03:53
So remember, that's the chunk that we want to insert in one go. That could be 100 records, 1,000, it doesn't really matter. The next thing is going to be the kind of unique thing that we want to look out for so we don't insert these records.
04:05
And the next bit is going to be the actual data we want to insert. Now let's just go and think about the columns to map that we pass through to here. So if we look for columns to map, remember, this array, we build it up by collecting this through and creating this data out and it's got the key and the value.
04:24
So remember, we've got ID and then ID from the CSV. So effectively, what we want to do here is collect these columns up. So let's say collect this columns to map. In fact, I'm going to change the name of this to columns because within the context of this,
04:41
it doesn't make sense to be called columns to map. So let's change that over. So we're going to collect these up and we're going to go ahead and do a diff on these with the ID.
04:50
That's going to get rid of the ID column and then we're going to say keys and to array. So they're the columns that we want to insert. We could actually die dump on this just to see or we could log this actually just to see what we get.
05:04
So let's do that first of all, just so it makes complete sense. There are a lot easier ways to do this, but if you don't use any other tools, then log is the way to go. So we're going to log that out and let's comment this out.
05:16
Let's head over to our Laravel log file and just delete all this and let's see what we get when we do this. So we're going to head over, upload a file, choose this and map these up nicely and we're going to hit import.
05:31
Okay. So let's head back over to our Laravel log file and you can see here that we get first name, last name and email. So basically all this means is from this chunk that we've passed in here, which contains
05:42
the ID, we're not going to insert if the ID matches, but we are going to insert the first name, last name and email. That's why we get rid of the ID because we don't want to reinsert the ID. Okay.
05:53
And the reason for that is if, for example, you have your database sequence ending at a thousand, you want the next one to be a thousand and one. You don't want that to be replaced by something that potentially already exists. So we're just getting rid of the ID so the database can handle creating the next auto
06:09
increment value for that. Okay. So there we go. We've upserted that data.
06:14
Now it's time to test it out. The last thing I am going to do is just add a sleep in here. It's quite sensible to do this so it doesn't throw a huge amount of inserts at your database. So we're going to sleep on this just to give this a break in between and we can test it
06:28
out now. So the first thing that we're going to do is head over to the customers table and we're going to go ahead and truncate this. We don't necessarily need to restart the identity because that's going to insert, but let's
06:39
go ahead and restart that anyway. And there we go. So we've got an empty table. Of course, when we refresh this page, we've got an empty list of customers and we're going
06:48
to go ahead and upload this CSV and have it processed. Now, of course, we're not going to get any progress at the moment because we've not worked on that yet, but we'll get to that soon. So let's import all of these records, hit import and just wait for this to finish.
07:02
And it should be fairly quick. So if we head over to our database, we should start to see these roll in if we don't have any issues. So we can check horizon, see if there are any failed jobs.
07:14
This might not actually be running. Maybe it is. Let's head back over to our database. It doesn't actually look like it is working.
07:21
So it looks like this has finished, but nothing has actually been inserted. OK, so let's have a look here. We may need to go ahead and just restart horizon. I think that may be the issue.
07:34
Let's just try this one more time because we obviously changed that class. Let's go over and hit customers again, go ahead and choose all of these to map up and hit import. OK, let's just have a look here.
07:47
We'll see this processing over in horizon and that's working through each of them with a one second delay. And there we go. Yes.
07:56
So we just need to restart our queue. So when we update a job within Laravel, if we have our queue working, we need to go ahead and restart that to have the changes take effect. OK, so now if we head over to our database, sure enough, we have 100 rows with all of
08:09
these in. Now, to really quickly demonstrate the upserting of this, what we can do is go ahead and import this again. And what's going to happen is because all of these IDs are going to match what's already
08:20
in the database, these aren't going to be imported. So let's do first name, last name and email. All of the IDs match. So we can let this run through again, just open this up, wait for each of them jobs to
08:31
go through. And sure enough, if we head over to the database, we still have 100 rows because each of these are matching. If we were to tweak one of these IDs manually, you would see the same record imported because
08:42
the ID doesn't already exist in the database. But once again, you can change around how this works. You can do pretty much whatever you want. If you want to just do a full insert and just ignore any of the duplicates, then that's
08:54
absolutely fine. Okay, so now that we've inserted these records, we need to really start to update our imports table with the particular import that we're currently working on. So let's go and do that in the next episode.

Episode summary

In this episode, we're diving into the process of upserting records from a CSV file into our database. We start by setting up our ImportCsv job so it can handle inserting (or, if necessary, updating) large chunks of data efficiently. We talk through exactly what information this job needs: the import itself, the model we're adding data to, the actual chunk of records, and the columns we want to use.

You'll see how we use constructor property promotion in PHP to keep our code clean and manageable. Then we look at upserting—meaning if a record with a matching ID already exists in the database, we skip it, otherwise we insert it as new. We even touch on how you could change the "uniqueness" identifier (like using email instead of ID) if that fits your use case better.

We'll also cover some practical details, like batching the inserts to avoid overwhelming the database, and troubleshooting when it seems like nothing's happening (hint: don't forget to restart your queue worker when you make changes!).

And finally, we verify the upsert by importing the same CSV twice to show that duplicate records aren't created. This is a great foundation for handling CSV imports cleanly and reliably. Next up, we'll focus on updating our imports table so we can track the progress of each import job.

Episode discussion

No comments, yet. Be the first!