How To Query, Sort, and Filter by Pivot Table Data
In this lesson, we'll learn how we can query pivot table data. We'll then learn how we can also sort and filter our results by those pivot table columns as well.
- Author
- Tom Gobich
- Published
- Apr 05
- Duration
- 9m 47s
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
How To Query, Sort, and Filter by Pivot Table Data
-
[MUSIC]
-
Traditionally on these movie listing sites, the actual movie show page is usually in
-
charge of showing the writer, director, as well as the cast members, crew members,
-
and things like that.
-
So today, let's go ahead and add in our cast and
-
crew members to this page as well.
-
We'll go ahead and put them underneath the abstract, but
-
first we actually need to get them queried.
-
So let's hide our browser away, and this will be inside of our movies controller.
-
So open that up, scroll down to our show method, and
-
here's where we want to add those queries in.
-
So we'll probably have our cast members listed out first, so
-
let's go ahead and query them first.
-
Again, just like our capabilities of loading in our director and
-
writer, we could do this directly with the query builder.
-
However, here we're just doing a find by or fail.
-
So instead, we can reach through the relationship of our movie record and
-
get to them that way.
-
So we can do const cast equals await movie.related.
-
We're looking for our cast members, so we'll reach for our cast members
-
relationship and get the query builder specifically for this movie's cast members.
-
We'll provide those cast members into our page, and
-
then let's go ahead and jump into our page, and let's get them listed.
-
So we'll scroll underneath where we have our abstract, add in an extra div,
-
class, flex, flex wrap, justified between, gap of four.
-
And then inside of this div, let's do another div, class, width,
-
full, lg, width, one half, and let's go ahead and duplicate that.
-
So for the first one, we'll put our cast members, and then for
-
the second one, we'll put our crew members.
-
So we'll go ahead and label that accordingly.
-
So we'll do an h3 class, font, bold, text, lg, cast.
-
We'll copy that h3, paste it in this div, and switch it to crew.
-
Okay, now inside of each of these, we'll want to have our definition list
-
actually listing out our cast members.
-
So for this, we'll do another div, class, flex, gap of maybe two.
-
And then we're gonna want to loop over that div.
-
So we'll do @each, sinist, in, cast, and then we can end our each there.
-
We'll do our definition title.
-
This will be the actual sinist's name, so sinist.fullname.
-
And then we'll have the data definition.
-
This will be the person that they played.
-
We'll leave that empty for right now, and we'll take a look at what we have.
-
So let's jump back into our browser.
-
Okay, cool, so we see our cast members right down here.
-
So, so far, so good.
-
Let's hide that back away.
-
And let's take a look at what the actual data is that we have on each one of these
-
sinist records.
-
So underneath our top div here,
-
let's just scroll down to the bottom of the page, and let's inspect our cast.
-
Let's open our browser back up and scroll down to the bottom of the page.
-
All right, now we're seeing the underlying model instances for
-
each one of the cast members that we queried.
-
We see first name, last name, headshot URL, all that looks right.
-
We continue scrolling down.
-
We'll get down to our extras.
-
That's what we're looking for right here.
-
And we can see these pivot columns as we've previously covered
-
within this extras object.
-
So we can reach for our pivot character name to get the character name that this
-
particular sinist played within this movie.
-
But let's also note while we're here that we need to account for
-
the pivot sort order whenever we're actually querying the cast,
-
because we'll want the cast to be listed according to the sort order.
-
So we'll take care of that as well.
-
Okay, get that back away.
-
So we know within our data definition here,
-
we want to display the character that the sinist played.
-
And we just saw that we can do that via sinist.$extras.
-
And then this property is prefixed with pivot because it came from our pivot table.
-
And since it came from our pivot table and
-
we don't have a model representation of it,
-
the name of the property is as it's defined inside of the database,
-
which is in snake case.
-
So we'll do character_name.
-
All right, let's also make this a little bit muted.
-
So let's do class=text_slate, maybe 600.
-
Okay, let's take a look at that real quick and see how that looks.
-
All righty, so we have the sinist full name, and
-
then we have the character name that that sinist played within this movie.
-
I think we got that backwards.
-
I think most sites list the character name and then the actor, or
-
in our case, the sinist who played them.
-
So let's switch that real quick.
-
So we'll just cut that out of our dt, paste it into our dd, and
-
then cut that part out of our dd and paste it into our dt.
-
Also gonna make this text small and maybe 400,
-
just to give it a little bit of an extra difference.
-
Okay, jump back into our browser, that looks better.
-
We'll also add in an items baseline, just so
-
that the bottoms of both of these texts align.
-
Okay, so next we need to account for our sorting.
-
So so that we can easily see this, I'm just gonna map over our cast members,
-
cc, and then we'll just serialize it real quick, just so that we get all of the extra
-
properties for our model instance out of the way.
-
And we can just see the underlying properties that we're looking at.
-
Okay, so within our meta, pivot sort order, zero there.
-
Then we go to two, which is definitely not right, and then to one, and
-
then lastly to three.
-
So we definitely wanna fix the sort order here within our cast.
-
So let's hide that back away, jump back into our movies controller.
-
And the way that we can do this is by off of our query builder,
-
doing an order by, and then specifying that we wanna order by the pivot sort order.
-
Just the same as we're referencing the column inside of our extras object.
-
So if we give that a save, I wasn't happy with my formatting, but okay.
-
Jump back into our browser, give it a refresh, and there we go.
-
So now we have Damon Stanton, Nathan, Julian, and
-
Davin in the order with which they should have been.
-
So if we scroll through this now, we have our pivot sort order at zero,
-
then we go down to one, then two, and lastly, three.
-
So everything is listing out correctly there.
-
While we're on our pivot columns though, let's stop for a second.
-
So if we jump back into our movie model, the reason that these pivot columns are
-
plopped automatically on our extras and within our query results is because we
-
have the pivot columns defined on this relationship.
-
Here we're on our cast movies relationship for our cast members.
-
We did not apply that to our crew members.
-
So we're gonna need a way for our crew members to provide that we want their,
-
I believe it was their title for their job title for the movie,
-
as well as their sort order as well.
-
So within our movies controller here, whenever we go to replicate all of this
-
for our crew, if we just give that a copy, do our crew, provide our crew into our
-
page, we also need to switch that relationship to crew members.
-
Let's jump back into our page.
-
We'll switch this from cast to our crew.
-
We'll scroll up, give our definition list here a copy and a paste, and
-
switch our loop from cast to crew.
-
And for right now, let's just get rid of our DT.
-
Okay, we'll give that a save, jump back into our browser, and
-
you're gonna see immediately that we get an error because the column pivot sort
-
order does not exist within the queried fields.
-
It exists on our table, but Lucid didn't include it inside of its query,
-
therefore it doesn't actually know about it.
-
So we're gonna need to include it inside of that query in order for
-
us to order by it.
-
So we can jump back into our movies controller.
-
On our query, we'll break that down into a new line.
-
There is an option called pivot columns, where we can define the exact same
-
specification that we have on our movie model for the pivot columns right here.
-
We could just do it on a per query basis because we may not need this information
-
every time that we're querying our cast and crew.
-
So you have the option to either always include it via the relationship definition
-
or include it per query via the query builder.
-
So our crew members have a title as well as the sort order
-
that we'll want to pluck off of the pivot table.
-
Now with the sort order included as a pivot column, it should get included
-
inside of our query and allow us to order by that pivot sort order.
-
So if we give this a save, let's jump back into our browser real quick and
-
make sure that that fixed everything.
-
So we'll give this a refresh and cool, we're back.
-
We have our crew members now.
-
We don't have their title in there quite yet.
-
But if we scroll down, we'll see immediately in the meta,
-
the pivot title is available there.
-
So we can pluck it and it's readily available.
-
But our pivot sort order starts at zero now.
-
If we scroll down, it goes to one next, scroll down some more, it goes to two.
-
And that's the last one.
-
So everything's sorting there A-OK.
-
Let's scroll back up and let's get their title added in.
-
So we jump back into our show page.
-
Let's go back a couple of steps to get our DT back in here.
-
We're gonna want their full name to be the primary title for the definition.
-
So we'll copy that and paste that into the DT.
-
For the DD, we'll wanna do Cineast.$extras.pivot_title.
-
Since we're plucking a pivot column and
-
the column's name itself is title on the pivot table.
-
Jump back into our browser and there we go.
-
So now we have Caesar was a gaffer, Kelly was a sound mixer, and
-
Jocelyn was a sound mixer as well.
-
So now before we move on from pivot columns, let's go ahead and
-
talk about how we can actually filter by them as well.
-
So let's hide away our inspect call.
-
We're done with that there.
-
And let's jump back into our movies controller.
-
And we had two sound mixers and one gaffer within our crew.
-
So let's go ahead and filter our crew down to just sound mixers.
-
For an example, we can chain on our query builder, a method called where pivot.
-
And you'll see within the autocomplete here,
-
we have pretty much every representation of the where clauses available specifically
-
for pivot columns.
-
So almost anything that you can do with a where statement,
-
you can also do with a where statement for your pivot columns instead.
-
And essentially what that's going to do is prefix any column name that we provide
-
as the key with that pivot_ prefix that we've been working with so
-
far with our pivot columns, just like right here.
-
So we'll automatically add that in for
-
us whenever it's applying this where pivot clause.
-
So if we wanted to filter the crew members title down to just sound mixers,
-
we would just provide title here and Lucid will take care of the rest.
-
And then for the value, we can filter this down to sound mixers.
-
Actually, that was singular.
-
Let's fix that real quick.
-
Okay, let's jump back into our browser, give that a refresh.
-
And now our crew went from three people down to just these two.
-
We're down at the bottom of the page here and our gaffer is now gone.
-
If we remove that where pivot, jump back into our browser,
-
give it a refresh once more, our gaffer is back.
-
So know that when you're working with your many-to-many relationships,
-
you can use those where pivot clauses to filter down specifically by your pivot
-
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
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!