Filtering, Preloading, and Sorting By Relationship

In This Lesson

We'll list the movies a user has watched on their profile. To do this, we'll filter, preload, and sort by the watchlist relationship where the user's watched at value is not null.

Created by: @tomgobich
Published: 1 year ago

🕰️ Chapters
00:00 - Querying Watched Movies
01:15 - Showing When The Movie Was Watched
03:35 - Joining Watchlist To Sort By Watched At
05:30 - Checking Our Work

Join the Discussion 0 comments

Be the first to comment!

Transcript

[MUSIC] Next for our avatar, what we're going to want to be able to do is list out
the movies that the user has watched. So let's hide our browser back away, jump back into our profiles controller. So that we don't have to write things twice, I'm going to go ahead and get rid of just this extra at.
Since we're using IDs, it doesn't make a whole lot of sense for our application. Jump back into our routes and we can just get rid of that route altogether. We'll just use this profile/id going forward.
So the first thing that we're going to want to do is const movies equals await and we're going to want to query our movies. So we'll import that model here, that query.
Now what we're after are movies that the user has watched. So for that, we need to reach into our watch list relationship.
So we'll do where has a watch list for the movie. As the movie must be in our watch list in order for them to mark it as watched, we'll reach for the inner query for this watch list,
check and do query.where not null watched at. Essentially limiting our movie results back to those that the user has watched.
Now right now this would be any user inside of our application that has watched this movie. So we also want to do query.where and then provide in
the user.id of the user for the profile that we're showing. Let's give that a save so that it reformats. Now we can add that movies into our page states,
jump back down into our profile show and uncomment this movies block right here. We jump back into our browser to see how this looks. You can see that we get back the three movies that John Doe Edit 3 has watched.
Next, what would be greatest actually show when they watched this movie. So let's go ahead and hide our browser back away. To do that, we're going to need to jump into our profiles controller.
This where has is just limiting the movies where this user has it in their watch lists and where that user has also watched it.
But that won't actually populate the watch list onto the movies. Instead, we need to also preload the watch list as well.
You query, query.where user ID is user.id. Since any one user can only have any one movie inside of their watch list,
we don't need to worry about the where not null since we're already checking that on the where has. With the watch list now added onto our movies, let's go and jump back into our profile show page.
We've dictated the card mostly around the authenticated user. For example, if we were to jump back into our browser, this may not necessarily be us that we're looking at,
but now we're seeing that toggle to add or remove this to our watch list, which we don't want for this particular page. So let's go ahead and hide this back away. Let's duplicate our card.
So we'll jump into our movie card, go into there. Let's just give everything a copy and let's create a new card_profile.h and paste everything back in.
We'll scroll on up, get rid of our form just like so. We'll leave the slot action in and now that should get
rid of everything that we had specific for the authenticated user. While we're in here, we also want to add in when the user watched a movie.
So let's just add in a paragraph class, text slate 400, text extra small,
py4 watched at and then we can reach for movie.watchlist at zero. So grab the first watch list item.watched at,
and that should be a reference to a date time from Luxon. But let's stop right here and swap in this card on our profile show page, just to see what we get first.
So card, add profile onto the end of that, give that a save, just jump back into our browser, and there we go. So we can see, watched at, and then we get back our time string just like so.
So let's give that a quick format. Jump back into our card profile and call.to format, maybe MMM to give a short abbreviation of the month,
day day, year year year year, and then maybe hour hour, minute minute. I think I got the tokens there, right? Let's go ahead and see how that goes. Yeah, that looks about right.
Cool. So next what we want to do is add sorting to this because right now, it's just going from how it's inserted in the database, which isn't very reliable.
Rather, it would be great to show the most recently watched to the oldest watched. But since that seems to be the sort that it's going in already at present,
let's do the inverse. Let's go oldest watched to most recent watched, just so that we can see that inversion happen. Jump back into our profiles controller. So as we previously learned,
in order for us to be able to actually sort by our watch list relationship, we're going to need to join it in. So we'll join in our watch lists table.
For our join, we're going to want to do watch lists.movieId equals our movies.id. But we also want to filter this down a little bit further.
So where watch lists.userId also equals our queried usersId, the profile that we're trying to show as well.
This will then allow us to do our order by watch lists.watchedAt, and we want to go oldest watched to newest watched so that we see some change. So we'll do ascending there.
Then we just want to select back our movies.star to essentially remove what we've joined in and just select our movie results. So since we've joined in our watch lists,
we're using watch lists. to specify that we want to access the userId off of the watch lists table that we've joined in. So that's why we're now doing the.syntax for the where and the order by,
as these columns that we're wanting to query and order by are on our watch lists table. They are unique, so we shouldn't necessarily need to do that.
For example, we don't have a userId or a watch that on our movie table, but for clarity sake, it's always good to put that in. Okay. Let's go ahead and give that a save.
And if we did everything right, we should be able to now jump back into our browser. Okay. Give it a refresh. And we see our watch that now goes May 11th,
2024, 11/22, 11/22, 11/24. So it's going oldest watched to most recent watched. If we jump back into our text editor,
switch this ascending to now descending, jump back into our browser and give it a refresh. We go most recent watched to oldest watched. So now our sort is being applied.
Awesome. Let's also add in a little header on our page two, just to know what it is that we're showing. So we'll do something like maybe an H4,
movies, user.null name as watched. Let's also add a class, font-bold, margin-bottom, three to that as well. Okay. Let's see how that looks.
So everything there is looking a-okay. I think we have our user profiles ready to go. Let's go ahead and verify real quick though that everything is user-specific. So let's log out of John Doe.
Let's register a new user. Do my name there. Do test. I don't remember where we left off on our numbers, so I'll just do test plus Tom and some password there.
Okie-doke. I'm going to add a couple of movies here to my watch list just randomly. Okay. That should be good. Go into our watch list now. Looks like a couple of our images failed to load,
and I'm just going to mark a couple here as watched. So how about these two? All right. So now that we have a couple of movies here marked as watched,
I'm going to go into profiles/ and this user's ID is 11. There we go. So now we have three movies watched. We have 310 Yuma, Smooth, and Cars.
Each one has a May 25 date on it, so that seems to be a-okay, and they're going most recent watched to oldest watched. We do have a null right here. Let's take care of that real quick.
So we'll just jump back into profile show and wrap our paragraph tag in an if. So if user profile description and if paste that back in.
There we go. Let's log out real quick, and then let's go back to login. Let's go back to the user that we were testing with here earlier. So go back in here.
All right. Now go into profiles/7, which is their ID, and you can see Watch That is still May 11th for 310 to Yuma,
which is one movie that both of those users shared. Awesome, so it's not a whole lot, but there is our user profiles.

Let's Learn AdonisJS 6

113 Lessons

14h 15m

Introduction

Fundamentals

Building Views with EdgeJS

Database and Lucid ORM Basics

Lucid ORM Relationships

Working With Forms

Authentication & Middleware

Filtering and Paginating Queries

User Watchlist

User Profiles

Admin Panel

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.