Joining SQL Tables To Order By A Related Column
In this lesson, we'll learn how to join a related table into our query and use that table to apply a sort via the relationship. We'll then see how we can conditionally apply this join only when it's needed for the selected order by.
- Author
- Tom Gobich
- Published
- May 01
- Duration
- 4m 49s
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
Joining SQL Tables To Order By A Related Column
-
[MUSIC]
-
Okay, so one thing to note for our sort is that if we want to sort by a property
-
that's not directly on our movie model or our movie table, then we're going to need
-
to join whatever that property is into our query.
-
Otherwise, we're going to get a SQL error, because in order for SQL to be able to
-
order by that property, it needs to be included within the queried fields.
-
So for example, if we were to try and order by, say, the writer's name in
-
alphabetical order, we would need to join that writer into our results in order for
-
that to work.
-
We're preloading, yes, but that is a separate queried entity from our actual
-
movies, which is what we're using to order by.
-
So first, let's go ahead and add the option in to order by our writer name.
-
So id writer ascending text writer name ascending field, and we're going to need
-
to make this join friendly.
-
So Cineasts.name to get our writer's name.
-
So Cineasts is the table and the name is the column within that table that will
-
ultimately want to order by.
-
And then the dir will be ascending for this option.
-
Give that a quick copy and a paste and switch these to descending here, descending.
-
And OK, we're seeing a red squiggly here because Cineasts.name is what we anticipate
-
the join name to be, and that's not a direct key of our movie.
-
So we'll need to loosen that type up a little bit and we can just switch that to
-
string.
-
OK, that will make our options here happy as well as our movie query, because our
-
where clauses won't specifically care about that.
-
So we could always have this joined in or we could conditionally joined it in.
-
So since the logic for this will be a little bit easier to follow if we just always apply
-
this join in, let's go ahead and do that first.
-
So let's first go ahead and just join our Cineasts table in and then we need to specify
-
the primary column as well as the secondary column.
-
This is essentially just building out that on clause within the join statement.
-
So we'll do Cineasts.id because that's what we have to match against on our movie table.
-
And then for our movie table, we could do writer_id.
-
With these joins, we need to specify things as they'll actually be defined within the
-
underlying SQL.
-
So we'll do writer_id because that's how it's defined within our database.
-
And now we have our Cineasts joined directly into our movie query.
-
In order for us to be able to get our movie back into our movie model, we're going to
-
want to select just the movie results.
-
Now, the ordering for these doesn't really matter.
-
The query builder is going to order them as they need to be.
-
But let's go ahead and next add in that select and we'll just select our movies.star,
-
selecting all columns from our movies table.
-
OK, so if we did everything correctly here with that saved, we should be able to jump
-
into our browser.
-
We'll need to give this a refresh to get our new sort options added in.
-
And let's try to select our writer_name ascending.
-
If we search with that, all right, we do get an error.
-
Select movies from movies, inner join Cineasts on Cineasts.id equals writer_id where title
-
I like one and status ID equals two.
-
Those are our two search and status filters.
-
Order by Cineasts.name ascending limit, the parameter for our limit there.
-
Column Cineasts.name does not exist.
-
And that's very true.
-
So if we scroll back up here to our sort options, our Cineasts doesn't have a direct name or
-
even a full name.
-
Instead, they just have last name, first name.
-
So let's switch that name to just last name there and last name there.
-
If you wanted to be able to do last name and first name, you could alter this field here
-
to allow it to be an array of fields to apply to and then loop over that for the order by
-
we'll go ahead and just leave it as the singular field here.
-
So let's give that a save.
-
Jump back into our browser, give it a test once more.
-
And there we go.
-
So it seems like everything works here.
-
We now have for our writers, Natasha Bogan.
-
Then we go in the Kremen, Fist, Kutch, Rayner, and so on and so forth.
-
So it does seem to be in alphabetical order there by the last name of our writer.
-
If we switch this now to descending, it's going in the opposite order.
-
So awesome.
-
Everything there is working a-okay.
-
So just keep that in mind as you're going forward.
-
If you need to apply a sort for a field not directly on the table that you're querying,
-
you will need to join that in to be able to sort by it.
-
And then you can get back the applicable movie model result that you need by just
-
selecting in that models columns.
-
We don't always need to join the synesthene.
-
We just need it whenever we have our writer ascending or descending sort options selected.
-
We could conditionally apply that join as well.
-
So we could do an if right here with the IDs, which are writer ascending and writer underscore
-
descending.
-
So writer underscore ascending and writer underscore descending.includes our sort.id.
-
Then that's where we'll want to take our query and then join in our synesthene.
-
And we could leave the select in here.
-
It's not going to change anything in this particular case.
-
But just to show that ordering here doesn't really matter and that the query builder will
-
fix however we order this, we'll go ahead and apply the select only whenever we have
-
one of these two filters applied here as well.
-
If we give that a save to fix the formatting, jump back into our browser and give it one
-
more test.
-
So let's just do a refresh here because we already have writer descending applied.
-
Let's do writer ascending now.
-
And then let's go back up to just our movie title and voila.
-
So now our writer will only be joined in whenever we're specifically ordering by the
-
writer when it's needed.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 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 7s
-
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
-
Validating Query String Filter Values7m 23s
-
How To Paginate Filtered Query Results9m 15s
-
Pagination First, Last, Next, and Previous Buttons4m 2s
-
-
User Watchlist
-
An Alternative Approach to Many-To-Many Relationships4m 56s
-
Toggling A Movie in an Authenticated User's Watchlist9m 56s
-
Listing and Filtering User Watchlist Items7m 34s
-
Allowing Users To Toggle A Movie As Watched4m 44s
-
Filtering By User's Watched Status6m 7s
-
Defining A Composite Unique Constraint4m 46s
-
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!