In this chapter, we're going to go ahead and implement video reactions. It's going to be very similar process as our previous video views. So we're going to start by creating video reactions schema. After that, we're going to go ahead and combine video reactions to our GetOneVideos procedure. We are then going to create the procedure to add like and dislike, and then finally, we will connect our video reactions component to the new API.
So let's go ahead and start by creating the video reactions schema. I'm going to go ahead instead of source inside of my database schema.ds and I'm going to go ahead down here and I will copy my entire video views because it's going to be very similar. So let me just paste it here at the bottom and I will change this from video views to video reactions like this. And this will be changed to video reactions as well. It's going to have a required user id, a required video id as well and one more field which we have to define an enum for above so on reaction type and let's make it pg enum which we already have reaction type like and dislike like this so make sure you have pg enum You should already have it because we have the video visibility.
You can import it right here. Great. So we now have the reaction type. And now we can go ahead and add type reaction type type and let's go ahead and make sure it's required whenever we create the video reaction method And now we're going to create a primary key called a video reactions primary key using the combination of user ID and video ID. There we go.
So we now have our video reactions. Now let's go ahead and let's copy video viewer relations. Again, just a reminder, this is application level only. It will not modify your SQL. So if you're having problems with building these relations, you can skip them.
But let's go ahead and do them. So this will be video reactions, video reaction relations. It's going to use video reactions here. And now we're going to modify these two. So the user will be one and it will target the field user ID and reference to the user's schema ID.
It will have one video as well, two video reactions. And then we're going to have our usual three exports here. Video reaction select schema, video reaction insert and update schema. Make sure all of them are using the new video reactions. Don't accidentally reuse the video views one.
And now what we have to do is we have to add them to the user and we have to add them to the video, right? So video views, now we're going to have video reactions, many video reactions. There we go, so that's it for user relations. And now let's go ahead and do the same thing for the videos here. Let me just find, oh, here are my video relations.
Looks like I've commented it out in the previous chapter, but yeah, we can use them, right? Views, many, and reactions, many video reactions. There we go. So we now have added reactions to our project. And now let's go ahead and do Bonnex Drizzle Kit push like this.
And I will just open my studio, just to do some sanity check to confirm that your database looks the same. So there we go, changes applied, no errors. Let's do Bonnex Drizzle Kit Studio. And we should now have it running at local Drizzle Studio. And I just want to show you the tables that I have.
So I have video reactions, which have user ID, video ID, type, created at, and updated at, and here are the relations to the users and the videos. And in here I have video views. I have a single record of the user that watched the video, the video that they watched and the timestamps. And I have relations, my user John Doe and my video called Untitled 1-3 with AI generated. Actually, no, this is the description which we use to demonstrate three lines.
So that's the only video that I have. So just ensure that yours looks the same. If it doesn't, I'm going to go ahead and just show you snippets of my schema here again so that you can confirm that you didn't do any mistakes here. There we go. Perfect!
So we have this already and now what we have to do is we have to go back inside of our modules, inside of our videos, inside of server procedures right here. And inside of get one base procedure we now have to find a way to add how many likes this video has received. And this one will actually be quite easy, right? We already know how to add view count. So how would we add a like count?
Let's try that. Like count will be database, count video reactions like this. And then we would use the end operator here. So let's just import video reactions from schema. Make sure you have added the new video reactions here.
And I'm just going to collapse these because it's gonna be easier to look at multi-line. And instead of here, I'm going to add two equals. The first one will be checking if the video reactions, video ID matches our videos ID. And the other one will be if video reactions dot type matches like. There we go.
So don't make a mistake here you know don't accidentally pass input dot ID here What you have to pass in here is videos.id because we're building a subquery here. It will be understood at this point that the videos.id is whatever we will query by because we will only find a single result here, right? So we are passing the schema field inside of here. Great. So we have that.
And now what we have to do is we have to do the same thing for the dislike count. So let's add dislike count. And all you have to do is switch this to dislike. So that's good if I have a revered existing video and I have view count, like count, and dislike count. But I still have no idea what the currently logged in user actually did.
If I look here, I don't know which of these fields should I use to indicate in the UI whether the user liked or disliked this. We could do a separate query for this, but we can also learn how to use common table expressions, which we can then join and then use them in the select field. So let's go ahead and do that. This is what we are going to do. We're going to create const viewerReactions which will be a database common table expression which we're going to call viewerReactions like this.
And just go ahead and pass this for now. In order to actually build this method, we're gonna have to check if we are logged in or not. So what we're going to do is we're going to add context from here. And this base procedure will give us a potential clerk user ID. So that's the structure, clerk user ID from the context, and then let's check if we have the user ID.
So we're going to define it, And then let's get the user using await database, select from users. Looks like we already have users imported in the schema. So that's good. And let's do this, where? And now the only way I found to safely conditionally query for this is by using in array from drizzle ORM.
So this is a bit of a hacky method because if you used, you know, if I have clerk user ID, then do equals users, clerk ID, clerk user ID, otherwise undefined. This could potentially return all users from the database when someone is logged out. So that's not something we want. So what I opted to do was to use in array users clerk ID and then I check if I have clerk user ID, I will simply use that as the single item in the array. Otherwise, the array will be empty and no results will be returned back if we are not logged in.
So just make sure you have imported in array from Drizzle ORM and you should now be able to find this user here. And what we're going to do is we're going to check if there is a user assigned that user id here. And now we can go ahead and actually build this. So let's go ahead and do database.select. And we are only going to pass through the video ID.
So we're going to pass the relevant video ID that we found for this video reaction. So video reactions, video ID and the type. So we know what reaction this is, like or dislike. This will be from video reactions and where again in array, video reactions, user ID. And then we're going to check if we managed to load that user ID, we're going to add that user ID to the array, otherwise an empty array.
So we had to do this part because we need to compare it with the database user ID, not the clerk user ID. And now we have a common table expression like a temporary query here, which we will be able to join on here. So we will find the relevant video ID and then we will get the type. And we are only looking for a reaction which this user has made. And we are going to join only the reaction from that user and this video ID that we need.
So this is how that's going to look like. So we are going to use left join for that because there is a possibility that this record doesn't exist if the user never reacted to the video, right? So let's pass in the viewer reactions, pass in the equals, viewer reactions, video id, and videos.id. So this way viewer reactions will already by default only load the currently logged in user ID if it exists. And now we query even further by ensuring that it's only for this video's ID that we are querying for here.
And now that we have viewer reactions here, what we will be able to do is pass inside of our select method here, we will be able to pass the current viewer reaction. And we can do viewerReactions.type. So now if you go ahead and hover over here, you will be able to see that we have view count, like count, dislike count, and also the current viewer reaction, which can be like, dislike, or null if it doesn't exist. So that's how we use this single query with a common table expression to create an optimized and fast way to query all of those things and we are not doing any JavaScript joins here, we are using proper SQL joins. Great!
So that's all the info we need to build this part of the UI. Let's go ahead and see some errors. Looks like we do have some errors. Relation viewers, viewer reactions does not exist. The error will be omitted in production builds.
Perhaps we are missing some things here. I think what's missing is for us to properly group the information. So let me see if I am correct in here. First, let's check my viewer reactions, common table expressions. This I think is completely fine.
Nothing wrong with this. And yes, what's missing is whenever you do a common table expression, if you want to do an inner join on that table, like I did here, or left join, any join, it doesn't matter. You have to add with in the beginning here. So just passing viewer reactions here. And I think that we are still gonna have an error, but one error less, or maybe not.
It looks like we are not getting any errors, but I do think that we will have to group by our result here, so it's properly structured. So I'm going to add group by here, videos ID, users ID, and viewer reactions dot type, like this. So I don't have duplicate results. So now I think that nothing should change here we still have the user view count like count dislike count and the viewer reaction and no errors great so now we will be able to properly pass information to our video reaction component. But before we do that, let's go ahead and let's create a new module called Video Reactions and create the procedure for liking and disliking.
Just before I do that, I want to go back to this video's procedure here and specifically focus on this group by method. So usually, whenever you need group by, you would clearly see an error happening. So I'm having my doubts about needing this. I think that in my source code, I needed this group by because I didn't initially use the database count sub queries. Instead, what I did was I had a mix of counts and aggregated queries, and that basically caused confusion here, so I had to group by.
But I think that since my counts are a separate subquery, and my viewer reaction for now is the only commentable expression join that I have. I think we can console this out. I think that if I just console this out, there are no errors at all and I think that I'm getting the correct example. Let's see if I add limit one here as well which is you know technically correct. To ensure there are no errors perhaps then it will give me different results.
Looks like it's not, looks like everything works fine. So I will comment this out just because I think this might be redundant. So I'm going to leave it like this for now. If there is no errors, I think everything should be fine, but we are gonna see later on because we will add one more common table expression. So perhaps that will cause an enough mix of counts and aggregate queries that we need to group by.
Let's continue with our thought, which was, we can just copy video views actually because it's very similar. Let's add video reactions here and inside of video reactions go inside of procedures and call this video reactions router like this. And now inside of here, instead of create, we are gonna add a like procedure and instead of video views we will have video reactions from database schema. So this will stay the same, a single video ID. And this will be a protected procedure, so we will have the current user ID.
And let's start by having the existing video reaction here from video reactions like this. If we have an existing video reaction and also let's add another query here to ensure that this is a like reaction because it's a like procedure. So if we already liked this video and we hit this endpoint once, We're going to use it as a toggle. So I'm going to go ahead and delete the record now. So const deletedViewerReaction will be await database, delete from videoReactions, where and passing video reactions user ID matches my user ID and video reactions video ID matches the video ID.
Actually we can be more specific We can just use the existing reaction ID. Because we can only find it if the user ID matches. So how about we just do video reactions. Oh, yes, my apologies. I forgot.
Video reactions do not have an ID. They have a composite primary key of user and video. So that's what I was being confused about. Okay, so this should work just fine. And I'm not sure if we are going to need the deleted record.
Well, yeah, we will need it for the API. So let's just return deleted viewer record. That's it. The query will end here in case we already liked this and now this will be our way of not disliking it, but simply removing our like, right? We are removing our reaction.
And finally, let's do created video reaction here. Insert into video reactions. And we will pass in the type to be like. And pass in the created video reaction like this. There we go.
I see in my source code I actually used something interesting. I used on conflict do update which is very interesting. I'm not sure why I needed that. I added this on conflict do update like this, target video reactions, user ID and video reactions, video ID and set type to light. But I'm pretty sure that I did this before I implemented this, because this is like an absurd, right?
Perhaps I was trying to handle multiple things at the same time before. So I just wanna bring to your attention that did you do something on this before? And now I can't remember if this is something that we are going to need or not, because this definitely looks like an absurd of sorts. So if this exact thing exists, set the type to like. I think we actually do need this.
Yes, we absolutely do need this one. Here's why. We will only delete an existing video reaction if it matches the video ID, the user ID, and like. And then we're gonna go ahead and delete it. But what happens if we have a reaction for that video ID under our user ID, but we disliked the video.
In that case, we are not going to detect an existing video reaction and we're not going to clear it. And in here we will attempt to create a new video reaction and we will get a conflict because it already exists but under dislike. So what we have to do is we have to find that conflict and resolving by upserting to like. Yes, we absolutely need this method. This is the correct decision here.
In order to make this easier to understand, perhaps you could change this to existing video reaction like. And then in here, existing video reaction like. So that will maybe make it easier for you to understand. Okay, so I'm only resetting like here. I can still have a dislike, so I have to do proper conflict because of that.
And now we have to copy this method, like, and we have to add it here and call it dislike. And then we have to do the reverse logic here. So this will be an existing video reaction dislike. And we're going to check for dislike here. Everything else can stay the same.
If we have that dislike, we're going to simply delete that if they clicked on a dislike again, of course. Otherwise, if this is their first time clicking here, we are going to assume that they are adding a reaction for the first time. Let me just see, I have to dislike, type this properly. But in case they already have a like reaction, we're going to simply update the existing video of your reaction and change its value to dislike. So we definitely need the on conflict update here.
Created video reaction. There we go. That's our API for likes and dislikes. And now it's time for us to go inside of our video top row component, if I remember correctly, instead of videos, UI, components, video top row. And inside of here for the video reactions, let's add some props.
Video ID will be video.id. Likes will be video like count. Dislikes will be video dislike count. And viewer reaction will be video viewer reaction like this. So I'm gonna go ahead and create the interface for this now.
So let's go ahead and import video get one output from the types and let's create a video reaction props video ID likes, dislikes and viewer reaction. We can remove this to do for now. Let's go ahead and let's assign the props here. So we should now be able to extract all of this video ID likes, dislikes and the viewer reaction. So we can now remove this from here like this and now we should be able to actually pass in some expected data here so we can first of all change this thumbs down icon if your reaction is dislike there we go Now we should have no TypeScript error here.
And we can change this to Dislikes, and we can change this to Likes. And right now, both of them should be zero, and none of it should be selected. Great. And now, let's go ahead and let's develop some actual methods here. So we are going to protect this from being called if we are not logged in.
So let's add useClerk from Next.js so we can open that nice model. And let's go ahead and import utils. Actually, no, I'm sorry, why am I importing here? Basically, we need utils from trpc, from the client, use utils. So make sure you import TRPC from the client here.
And then we're gonna need to define our like procedures. So TRPC video. Oh we did not add video reactions to our underscore app routers. So let's just add video reactions and video reactions router here and just add it here video reactions video reactions router instead of the PRPC app. So this is our new video reactions, which we just worked on.
Now we should have video reactions like use mutation, like this, and we should also have dislike. There we go. And now let's go ahead and do the following. One cool thing about TRPC is it's on error field here. So what we can do is we can prevent, you know, like from being called immediately in the UI by checking, you know, if we are authorized, But since this is a mutation, which uses a protected procedure, but we technically allow anyone to trigger it, we can be specific if error data code is unauthorized, just make sure you use the question mark here, we can call clerk open sign in like this.
And we can also toast from Sonar an error something went wrong. Simply to have that generic error happen so that's on error and on success what we can do is do utils videos get one invalidate and passing the ID to video ID. And I'm going to add to do invalidate liked playlist later when we have playlists. Great, so we have this. And now I believe we can copy the entire thing and pass it in the dislike here.
Except, yeah, we will also have to do this because this will remove it from the liked playlist. Now that we have those two, let's go ahead and do this. Let's find the button for liking. Let's go ahead and give it an on click. Like mutate video ID.
And disabled will be if like is pending or if dislike is pending. So we don't spam and let's just copy them here, but use the We can just use this like mutate for this one. And I think everything else should be working now. So right now in my Drizzle Studio, I have no video reactions, as you can see. And Now if I click like, let's see if it works.
There we go. One like right here. And if I refresh my Drizzle Studio, I have a video reaction with the type of like. Now let's try the following. Let's try disliking.
This should now call our on conflict method, but maybe something went wrong. Looks like it just removed our reaction, so something is wrong. Let's confirm that we are actually calling dislike first. And we're not, of course. So dislike TRPC video reactions, dislike.
Make sure to add dislike here. Let's try this again. So now, if you do the same mistake as I did, it will be empty again. Let me try now. There we go, this works.
I will refresh. And I have type like. And you can see that my created at should not... The last three numbers are 666, unfortunate numbers, but let's remember them. And now if I try disliking, it should simply update that record.
It shouldn't create a new one. So if I refresh here, the type is now dislike, and we still have the same unfortunate number here. So it proves that it did that on conflict update, which we worked on, right? This happened in this like, when we hit this like, it did this, it detected that we already have that record. So let's just change its type to dislike.
But if I actually go ahead and click this and then change to this one, now what happened was it first deleted the record and then it created a new record. And we can prove that by seeing that we no longer have those three unfortunate numbers here. Perfect. So that is it. Looks like this is working.
There is one thing that's just bugging me, and that is our group by, right inside of here. Because I added this in my original source code and I'm 99% sure that I have added it for a reason. There was an error I was getting. I just can't remember if I resolved that error perhaps by changing my query further along or maybe I was getting an error because I had multiple users. Maybe the fact that I only have single records here is causing some problems.
So I'm going to go ahead and test that out before the next chapter. And I will come with some kind of conclusion for you. I just hope that you're not getting any errors. Like that is my biggest concern. In case you are, try enabling this.
Maybe that will resolve it. But I mean, rarely does this, you know, rarely do you need this without having some obvious errors going on, right? So that's why I'm doubting that we need it, but we will see, we will see. But I hope this was a cool exercise for you. So you now know how to create a comment table expression and how to add conflicting updates, all of those things.
And we have a pretty good experience here, right? You can see how fast this is because it's prefetched all from this one single query in the video ID page right here. We prefetch it only that and we get the video views, we get the likes and we even get the current video viewer reaction so we know if we liked or disliked ahead of time. It's super fast. Great.
So what we have to do next is we, first I will confirm that everything's okay with our grouping, and then I'm gonna go ahead and start developing the comments and the suggestions. But I think that before that perhaps we could implement subscribers. The reason I'm postponing that is because we don't really have the user page yet. So we don't really have a nice view. But yeah We'll see.
You're gonna see in the next chapter what we're gonna do, what I have decided. Great, let's just wrap up this. So we have added the video reactions schema. We combined video reactions here. We created like and dislike and we connected the component with the new API.
Great great job!