-
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
