Playing
03. Syncing view counts to the database
Transcript
00:00
We're storing our view counts, but we can't order on these at the moment, at least not very easily.
00:04
So ideally, what we'd want to do is something like the following. So let's pull out a query here. And let's say order by view count.
00:14
And we'd want to do that in descending order. So the most popular articles will be at the very top. Now, obviously, this isn't going to work at the moment, because we don't have a view count column.
00:25
Let's go ahead and add that in with a migration. And then we'll look at how we can sync this data from Redis back into our database. So let's go ahead and make out a migration.
00:33
And we'll add the view count to the articles table. OK. Let's go ahead and open that up, add view count to articles table.
00:40
And again, super simple stuff. We're just going to have an unsigned integer in here or an unsigned big integer, depending. And that's going to be view count.
00:49
Let's go ahead and set a default here of 0. So when one gets created, it sets it. OK. Let's go ahead and run our migrations.
00:56
And we're done. Now, this is going to work. But of course, it's useless at the moment, because all of the data in our database
01:02
is just going to have a view count of 0. So what we want to do now is periodically sync this data back into our database. So for this, we're going to use a command.
01:12
So let's make out a command here. And let's call this sync article view count. Of course, call it whatever you want. Let's go over to this command.
01:21
We'll leave the name of this exactly the same. Let's just go ahead and run it in our terminal. So we've got it in there. OK.
01:28
So what do we need to do? Basically, we want to go ahead and fetch all of the IDs for every single article that we've got created. And then into that, we want to go ahead and look up
01:38
the count within Redis for each of them IDs. We want this to be really efficient. So we're going to be batch updating this data as well. And we're going to need to put a package in for that.
01:49
So we'll look the count up with Redis. And then we basically want to batch update back into the database. So that's pretty much what we need to do.
01:58
OK. So let's get started on this and see what we do. OK. So let's go ahead and grab out a views variable here.
02:04
We're going to fetch the model that we want to work with. And for the purpose of keeping this as fast as possible, let's just select the ID, because we don't need any other data here.
02:13
And then once we've got that, let's go ahead and pluck the ID out. And that's going to give us back an array of all of that data. So let's just say to array, run this,
02:22
and we'll make sure that we dump these views. And there we go. We've got one, two, and three. So now with these views, we're not
02:31
going to cast them to an array. We're going to go ahead and map through them and pluck out all of the counts that we have within Redis and pass them back into this view.
02:40
So mapping is just going to modify that data. So let's go ahead and create our closure in here. We're just going to get an ID in, which is helpful. And then we're going to return out a structure
02:50
that we want to see and we want to insert back into the database. So we want to insert at the ID that we actually have for that particular article.
02:58
And then we want to go ahead and grab the count. So that could be any value. And this is where this comes from Redis. So now what we're going to do is we're
03:06
going to use Redis to do a PF count on that particular article. So again, we can use sprintf here, articles, and then the string for the ID, and views,
03:21
pass in the article ID. And that's it. So for each of the articles in our database now, we're fetching the count from Redis,
03:28
putting this into a structure that we want to see put back into our database, and we should be good. So let's go ahead and cast this to an array
03:35
so we can easily batch update our database. And let's go ahead and just dump on them views again and see what we get. OK, so we know that in our Redis store, we have 2, 1, and 0.
03:47
So when we go ahead and run this, we should see a count of 2, 1, and 0. Perfect. OK, so now that we've got this, we need to batch update this.
03:57
And for this, we're going to use this Laravel batch package. So let's go ahead and pull this in. It's very easy to use and see what we need to do. So let's grab the Composer command for this,
04:06
go ahead and pull this in, and let's start to use this. So this package comes with a couple of things that you can do, but I like to use the batch function here. And we're going to go ahead and update,
04:16
and we need to choose the model. We just need to new this up. So let's new up the model. And then because we've already got this in the structure
04:23
that we want to push it into based on the ID and the count, we just pass in the views. And then we say that we want to update that by ID. That is pretty much all we need to do to batch update.
04:35
So let's keep an eye on our database. We've got 0 view counts in there at the moment, of course. And we're going to go ahead and run that command again. And yeah, of course, I've put count there.
04:46
That needs to be view count. Let's try that one more time. And there we go. Let's check out our database, and our data has been synced.
04:55
So this is great, but we don't have to manually run this command every so often to keep our database in sync. So if we head over to our console file in our root,
05:04
this is where we can go ahead and schedule this command in. So let's go ahead and put in the schedule facade. Go ahead and use the command that we used earlier. So let's go ahead and find the name of that.
05:15
And we're just going to put that in there and schedule that for whichever period we want. So let's say that we want this to run every second, just so we can see this working.
05:25
We're going to go ahead and run phpArtisan schedule run, and you can see that it's being synced. Great. So if we head over to the database,
05:31
you can see, sure enough, that is keeping up to date. And obviously, if we lose any of that data and we run this again, that's going to go ahead and keep it up to date with us.
05:42
So it doesn't really matter when you schedule this for, but I'd say every hour at least, and you are good to go. Okay, so now we are fetching this data back from Redis, batch updating our database,
05:53
and of course, running this every single hour, which means what we can now do, if we head over to our reb root, is actually go ahead and sort this by the view count.
06:02
Give that a refresh. Of course, we see exactly the same thing. Let's go ahead and do this by ascending, just so we can see that this works nicely.
4 episodes• 22 mins•1 year ago
Overview
If you need to log unique views in Laravel, you might reach for a database table to track IP addresses or another unique piece of data.
Let's take a look at speeding things up both in performance and complexity by using Redis and the HyperLogLog probabilistic data structure.
Once we're done, we'll set up a period command to sync views back to the database for easy ordering, and then create a trait to share functionality between other models.
Alex Garrett-Smith
Hey, I'm the founder of Codecourse!
Episode discussion
No comments, yet. Be the first!