🕰️ Chapters
- Querying Watched Movies
- Showing When The Movie Was Watched
- Joining Watchlist To Sort By Watched At
- Checking Our Work
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.
- Author
- Tom Gobich
- Published
- Jun 06
- Duration
- 7m 6s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Filtering, Preloading, and Sorting By Relationship
-
[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.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 24s
-
2.1Rendering a View for a Route6m 30s
-
2.2Linking Between Routes7m 52s
-
2.3Loading A Movie Using Route Parameters9m 18s
-
2.4Validating Route Parameters6m 7s
-
2.5Vite and Our Assets6m 39s
-
2.6Setting Up Tailwind CSS7m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 8s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
7.0The Flow of Middleware7m 49s
-
7.1Authenticating A Newly Registered User4m 14s
-
7.2Checking For and Populating an Authenticated User2m 10s
-
7.3Logging Out An Authenticated User2m 24s
-
7.4Logging In An Existing User6m 54s
-
7.5Remembering A User's Authenticated Session6m 55s
-
7.6Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
-
8.0Creating A Movie List Page3m 43s
-
8.1Filtering A Query By Pattern Likeness7m 9s
-
8.2Filtering Our List by Movie Status5m 47s
-
8.3How To Apply A Dynamic Sort Filter To Your Query7m 12s
-
8.4Joining SQL Tables To Order By A Related Column4m 49s
-
8.5Validating Query String Filter Values7m 24s
-
8.6How To Paginate Filtered Query Results9m 15s
-
8.7Pagination First, Last, Next, and Previous Buttons4m 3s
-
-
User Watchlist
-
9.0An Alternative Approach to Many-To-Many Relationships4m 56s
-
9.1Toggling A Movie in an Authenticated User's Watchlist9m 56s
-
9.2Listing and Filtering User Watchlist Items7m 31s
-
9.3Allowing Users To Toggle A Movie As Watched4m 44s
-
9.4Filtering By User's Watched Status6m 7s
-
9.5Defining A Composite Unique Constraint4m 47s
-
9.6Persist Filters Easily with Lucid's Query String Method3m 58s
-
-
User Profiles
-
10.0How to Create and Fix Missing User Profiles in Your Application7m 37s
-
10.1Using Dependency Injection to Update A User's Profile9m 46s
-
10.2Saving All Or Nothing with Database Transactions5m 15s
-
10.3Uploading and Displaying User Avatars15m 29s
-
10.4Displaying A User's Profile6m 1s
-
10.5Filtering, Preloading, and Sorting By Relationship7m 6s
-
-
Admin Panel
-
11.0Creating An Admin Layout7m 14s
-
11.1Counting Stats for our Admin Dashboard5m 43s
-
11.2Paginated Admin Movie Table13m 2s
-
11.3Allowing Admins to Create Movies16m 39s
-
11.4Allowing Admins to Update Movies and Clear Values13m 27s
-
11.5How To Use One Form to Create or Edit Movies5m 32s
-
Uploading Movie Cover Images in our Create or Edit Form10m 29s
-
Using A Wildcard Route Param to Download Storage Images7m 57s
-
Posting Objects, Arrays, and an Array of Objects in HTML Forms26m 26s
-
Managed Transactions and Syncing Movie Cast Members15m 55s
-
Allowing Admins to Delete Movies and their Relationships7m 42s
-
Thank You for Watching!0m 31s
-
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!