In this chapter, our goal is to implement video views functionality. In order to do that, we're first going to start by creating the video views schema. We are then going to combine that schema inside of our get one procedure from the videos module, similarly to how we connected user using an inner join. We then have to create a video views creation procedure. And then Every time a video is played, we're going to trigger that view creation.
And then I will leave you at your own liberty to decide how do you want to count views. Do you want to count every single time someone refreshes the page or do you want to have one video view per user, that's it. Let's go ahead and implement this. So let's start by going and creating a video views schema. But before you do that, remember, if it's been more than 24 hours, your mux assets might have been deleted.
So just make sure that you have some asset you can be working with. Simply go inside of studio here and make sure you have Bundle Run Dev All running and upload a new video. Now, let's go ahead and let's go on this page right here. Let's start by creating a new schema. I'm going to go inside of my source, database schema.ts, and After my videos, after video relations here, I'm going to add export const video views.
And I will call this video underscore views. And then I'm going to have two foreign keys. First one will be representing the user ID. So let's go ahead and target users ID and let's add cascading rules on delete cascade, and then we're going to do the same thing for the video ID. That will be videos ID.
So if either of those two records get deleted, this video view will also get deleted. Let's make sure that both of these are required by adding not null. Now let's go ahead and let's add our usual created at and updated at timestamps here. And now what we have to do is we have to create a composite primary key. So I don't want to create a new UUID for this because I can simply use a combination of these two.
Let's go ahead and do that. We're going to go ahead and open this, open an array and add primary key here which you can import from drizzle or mpg core like this primary key and now we're going to use it here passing the name to be video views primary key and columns t.userid and t.videoid there we go we now have the video views So now let's go ahead and let's establish the relations. Remember, if for whatever reason you are having problems with this part, meaning that you're watching this video in the future and this relations API has changed. When we first built this type of relations, I explained that these are only on application level. This will not affect your SQL in any way.
But if you have the ability to write them, let's go ahead and write them for the pure reason in case some of you are using databases without foreign keys and you're wondering, okay, how do I create relations in that case? So let's add fields, video views.userID to match a reference, oops, Reference of users ID. And then we're just going to do the same thing for videos. So let's go ahead and change this to videos. Video views, video ID, and this will be videos ID.
There we go. And now let's go ahead and let's just add these three. Video view select schema, video view insert schema, and video view update schema. From create select, create insert, and create update, we have all of those here. Great.
So we now have these relations set up and now we have to add them to video relations and we have to add them to user relations. So let's go ahead and go inside of our videos here. Let's find video relations and let's simply also extract many here. Again, this is only an application level. If this is causing you any problems, you don't have to add this part.
I'm adding views, many video views Like this. And we have to do the same thing for the user. So let's go ahead and find the user. There we go. And I'm going to call this video views, many video views, like this.
So it doesn't really matter how you call it, I believe, in the relations here. You will still be able to ref... I mean, it matters if you will be using the relational query API, right? But just to make it clear for you, you don't need this for anything because we are not gonna be using their relational query API, but I just wanna show you how to use them in case you want to, right? But if they are causing you any problems, you can comment them out and everything will work exactly the same.
I'm just staying true to my source code. Great, so we have that. And I have added video views to user relations here. Great! And now let's go ahead and push those changes.
So Bonnex Drizzle Kit Push should update my new video views schema. Looks like we have an error here. Let's go ahead and see what it is. Column user ID appears twice in the primary key constraint. Let's go ahead and look at our video views here.
And this is the mistake. I did not rename this to video ID. There we go. Let's go ahead and change it now. Drizzle kit push and I believe now we should have no errors at all.
There we go. Change is applied. And now let's go ahead and let's create the module for video views. So I'm going to go inside of modules and I will create a new folder, video views. And the only thing this is going to have is the procedure.
So procedures.ts. Let's go ahead and let's import create TRPC router from TRPC init and let's add protected procedure here. Let's do export const video views router to be created TRPC router and the create will be a protected procedure. Passing the input which will come from Zod and the only thing where we'll expect is the UUID of the video we are trying to update. Let's add a mutation here, this will be an asynchronous method, and let's go ahead and extract input and context from here.
From the context, we can immediately destructure user ID like this. And we can also destructure video ID from the input. Let's find the existing record first, or we can call it existing video view to be more precise. And we're going to await database from add slash database, select from video views from database schema. So make sure you've added both of these two.
So we are now trying to find if a record for a view already exists. So my way of tracking views is going to be only for logged in users and only one view per user, right? So I'm not gonna allow users to create multiple views. You can of course tinker with this and create your own view tracking mechanism but this is the one I prefer. Let's go ahead and add video views video ID to be video ID from our input And let's go ahead and do video views dot user ID field to be our user ID like this.
So if this record already exists, We're not going to throw back an error. We can just return this existing video view back. The reason I don't want to throw an error here is because I'm not sure if... I mean, I'm certain that the TRPC's React query integration will cause this to refetch over and over and over again. That's not something we want to happen because technically there's no error happening here.
We could throw a 409. That would be a conflict. That would technically be correct. And I'm pretty sure that would still trigger a refetch on the React queries part. So what we would have to do is find a way to block that on the front end part.
So the call never gets called, right? But we can explore this. You know, I'm going to leave it like this for now because I think this is an okay solution and it's not really too wasteful. It will be called once per load, which is the same thing as if we combined it in the initial query of the get one video procedure. So it's not like we're wasting too many resources here.
So I think this is a fine enough solution. And now let's go ahead and click and create our created video view. We await database. This one will be insert into video views, values and passing the user ID and the video ID. And add returning here.
Let's fix the typo and let's return created video view here. There we go. That is our protected procedure for the video views. Now we have to go inside of our underscore app folder routers inside of the TRPC folder and we have to add video views, video views router from modules video views server procedures And now what I want to do is I want to go inside of my procedure for the video itself, so instead of server procedures, we have get one. And now what I want to do is I want to somehow query the views for this video.
So basically, I have to count this new table, which we created video views using this video ID. We could of course just simply do another query after we load this video. But how about we find a way to combine it all into one very nice optimized query. One way we can do this is by using a very useful database.count from Drizzle. So inside of this select for the get one, we can add video views and we can pass in database and use count with the dollar sign here.
So this is different than their count, which you can import from Drizzle, right? This is their database account. And this allows us to do a sub query from the video views schema. So I can directly call video views here and I can query it using your normal equals operator. Video views, video ID here, and videos ID like this.
And we don't have to inner join anything here because we are using this as a sub query, right? So this is, I think this is okay for a small query like this, which is basically just counting the records, but for more, for deeper queries, I would rather do a common table expression, which we then have to join and then query. But for things like this I'm okay with doing it like that. Alright so now each of our existing videos should have video views which are a number. And now what we have to do is we have to render that number on our VideoTopRow component I think, instead of Modules, we have Videos, UI, we have components.
I think that we have video top row here and we have the entire video here for us. And we can pass in the views here, video, video views like this and pass in here. Actually, I don't think I wanna call them video views. Sorry, I just blocked for a second. Yeah, I don't wanna call them video views.
Let me just go back to my procedure here. I'm gonna call them view count, right? Because later on we're going to have like count, dislike count, and all of those things. And basically every count query will be held in a key like this. So let's call this view count and then this will be view counts.
There we go. And pass in video view count here. And now if I refresh this, it should stay zero right here. And now we have to find a way so that when a video is played that we update it. So let's go inside of our views, video view here, and my apologies, let's go inside of the video section.
And what we're going to do is we're going to add the create view method from trpc video views create use mutation like this and let me also prepare is signed in from a wait actually use out from clerk next JS like this. And then what we are going to do is we are going to create our handle play method, which will check if we don't have user ID and break the method. Otherwise, my apologies, not if we don't have the user, if we are not signed in and break the method, otherwise it will call createView, mutate, and pass in the video ID, like this. So let me just keep this together here and we will also get our utils here from trpc.useUtils And inside of here, on success, we will call utils videos get one and invalidate. And pass in the ID, video ID.
So only this specific one will be invalidated. So I'm going to go ahead and call handlePlay onPlay here. And now I have zero views. But if I refresh this, let me just refresh, I should still have zero views until I click Play. Once I click Play, I should be triggering that API call.
So I'm just not sure if I did something incorrectly here. I still have zero views. I'm not sure if I'm logged in or not. Very weird, my user here is not appearing. I'm not sure when that disappeared.
Let's see what's going on here. Maybe I need to restart my application. I'm going to try and do that. Don't run DevAll. Because I have no idea why my user button disappeared all of a sudden.
Usually this is an indicator if I'm logged in or not. Let me quickly take a peek of my navbar here. Home, navbar, auth button. Yeah, we should definitely see the user button here. Okay, now I can see the user button.
I still have zero views, but if I play the video, is something, there we go. One view after I played the video because we successfully called API TRPC video views dot create. And after that, we re-invalidated, which basically means refetched, get one video, which gave us the new view here. From now on if I go ahead here nothing is going to happen, right? Because I will only have one view per video and per user.
Great! So you just learned how to combine another schema inside of our Get One procedure And what we're going to do in the next chapter is we're going to implement video reactions in a similar way. So you will be able to like and dislike. But that one is not going to be as simple, primarily because there are two records to keep track of, likes and dislikes. And also, we're going to have to store something inside of this get one procedure that will tell the user did you like this video or did you dislike this video or do you not have a reaction on this video at all so that we know which one of these to to highlight And we will also have to do that conditionally because this is a base or a public procedure.
So we might or might not have a user ID. So we have a very interesting query ahead of us, but we are going to do that in the next chapter. For this chapter, we can go ahead and safely say that we've created the video views schema, we combined the video views for get one videos procedure, we created video views creation procedure and we triggered video view creation on video play. Excellent! Amazing, amazing job.