00:05
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
00:12
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
00:19
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
00:27
alphabetical order, we would need to join that writer into our results in order for that to work.
00:32
We're preloading, yes, but that is a separate queried entity from our actual movies, which is what we're using to order by.
00:39
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
00:48
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.
00:58
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.
01:05
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.
01:13
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
01:22
where clauses won't specifically care about that. So we could always have this joined in or we could conditionally joined it in.
01:28
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.
01:34
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.
01:41
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.
01:50
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.
01:58
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.
02:05
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.
02:14
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.
02:23
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.
02:32
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.
02:42
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.
02:52
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.
02:59
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
03:08
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.
03:16
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.
03:26
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.
03:36
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.
03:44
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.
03:52
We just need it whenever we have our writer ascending or descending sort options selected. We could conditionally apply that join as well.
03:59
So we could do an if right here with the IDs, which are writer ascending and writer underscore descending.
04:06
So writer underscore ascending and writer underscore descending.includes our sort.id.
04:12
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.
04:21
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
04:29
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.
04:38
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.
