yeah, we just get a collection that's not paginated. So that makes it a little bit more tricky. And these sorts of situations where you want to basically join with another table and then order according to a relationship count, from my experiences, it's easier to just use a join. For instance, yes, we want to grab the community links, and we want to eager load the votes. And in fact, real quick before we get to this, notice that each link, yes, it delegates to the votes relationship, but it also finds its channel. And then it also finds its creator. So because we're not eager loading that for each iteration, if we come back, yeah, for each iteration here, it's going to perform another query. And if you enabled query locking, you would see that. So what you could do is just say also give me the creator as well as the channel. So eager load all of that stuff at once for me. Anyways, yes, we'll pass in a channel constraint, if there

that. So what you could do is just say also give me the creator as well as the channel. So eager load all of that stuff at once for me. Anyways, yes, we'll pass in a channel constraint, if there is one. However, we're going to left join. So join these together, but favor the community_links side on the condition that there is no match. So for example, left join the community_links_votes table on the condition that community_links_votes.community_link_id is equal to community_links.id. Right, just kind of a standard join here. So we're going to connect those two tables. And the glue that binds them is where we check to see if the community_links_votes table has a row where its community_link_id is equal to the id of the community_links table. That's how we join them together. So now we could say, let's get rid of this, get rid of that. And we could say select raw, because I know I'm going to use a count here.

community links table. That's how we join them together. So now we could say, let's get rid of this, get rid of that. And we could say selectRaw, because I know I'm going to use a count here. So anytime I know that I'm going to basically want to do something like this, often I will just delegate to selectRaw instead, and then just be thoughtful at what I pass here. So in this case, we want to fetch, we could be explicit, but let's still do kind of a star in this case. And then also the count of community_links_votes.id. So the total number of the votes as voteCounts. And then finally, we will order by the voteCount in descending order. So we're going to default to ordering by the voteCount just for now. But yeah, if we run this, it's not going to work just yet. So let's see what we got here. Whoops, looks like a missing comma. Anyways, yeah, if we dd this, yes, we have our paginator, but the collection only has a single

the very first link, you'll see that because we wrote our query in this way, you can see immediately how many votes it has without having to delegate to the relationship. But you can still do that if you want. And now we have the votes relationship. So with that in mind, you may not want to do something like votes in this case, where basically you're overriding that relationship. Well, in that case, links->votes will now be an integer rather than the relationship. So that's something to keep in mind if you do it in this way. And then yeah, of course, we have a groupBy anytime you have an aggregate or something like that, you want to make sure you specify how you want to group the results set by. So this works, but we are assuming that we want to sort by popularity by default, and we may not want to do that. So here's what we could do. We could do something like this orderBy and we could say, do we have a popular key in the query string?

by popularity by default, and we may not want to do that. So here's what we could do. We could do something like this orderBy and we could say, do we have a popular key in the query string? If so, then we're going to orderBy the voteCount. Otherwise, we will orderBy the updatedAt field. So now we could change this out to orderBy. And if we were to come back and refresh, now you can see that the most recent one is on top and the very last page should be the oldest one and it is. However, if we were to change this to popular, now you can see we're favoring popularity. So a quick note on this request exists. So if you did request, in this case, if you did request('popular'), well, in fact, let me just show you. That's not exactly what we want here, because it's going to return an empty string, which will evaluate to false. So request and then key is going to return the value associated with it. But in this case, we're not

here, because it's going to return an empty string, which will evaluate to false. So request and then key is going to return the value associated with it. But in this case, we're not doing popular equals 1 or anything. Really, we just want to add that popular flag and have it work. So that's why in those situations, we're going to say, does popular exist? We don't care what the value is, but was it specified as a key? And if so, we're going to order by the vote count. Otherwise, just when the article was last updated. But now you'll see a new problem here. So let's run it again and go to the next page. But notice the query string popular. Now we're on page two and popular is gone. So this is where you need to update your pagination. And you can do that really easy. We can say, to give you some real estate there, link, appends, request, query, and then give me the links. And that's it. So if we come back to Chrome, now it should work.

you can do that really easy. We can say, to give you some real estate there, link, appends, request, query, and then give me the links. And that's it. So if we come back to Chrome, now it should work. Let's try it. Show me the first page of popular results. And now show me the second page. And notice it did not remove that popular query. So yeah, that's a good thing to know. Whenever you want to retain an existing item in the query, you can either grab all of them or a specific item. This is something I do quite a bit. Okay, so looking good, definitely more complex than we had before. And this is the point where it just feels gross to be in the controller, right? So at these points, you can do a number of things. You could extract this to a single method on your CommunityLink class. That would be fine. But even then, this is kind of long and bulky. You can put it on a repository if you want. Or another thing I sometimes like to do with important queries that

community link class. That would be fine. But even then, this is kind of long and bulky. You can put it on a repository if you want. Or another thing I sometimes like to do with important queries that span a number of lines and might need to be modified often is I will create a Query object. A Query object is nothing more than a single class that performs a query. Like that's literally it. So imagine this. Imagine instead we said links equals. And how about new? And what's the query? Well, we want our community links. So you can name it like that or communityLinksQuery if you prefer that. I think that's pretty good. And then we'll fetch the results. Kind of cool. So this is one way. And of course, we'll be able to get rid of the orderBy statement as well. But this is one way that we could do this. And it kind of cleans up your controller. It makes this reusable if you need to use it elsewhere. So the shape of that might be something

as well. But this is one way that we could do this. And it kind of cleans up your controller. It makes this reusable if you need to use it elsewhere. So the shape of that might be something like app. Maybe you put it in a queries folder. And we'll call it CommunityLinksQuery. Let's set this up. namespace app\queries class CommunityLinksQuery. And then once again, the get method. And I can paste that in like so. And let's see. We can return that. And then let's use CommunityLink at the top. get will accept the channel. Or we could put that in the constructor. And that looks good. So let's see if that all works. We're going to load the first page. Ah, CommunityLink, we forgot to import it, of course. All right, use that at the very top with a little macro I have. Give it a refresh. Undefined variable channels. Okay, let's see what the problem is. Whoops, I'm sorry. I named that wrong. We're just trying to pass through any channel that is specified or

Give it a refresh. Undefined variable channels. Okay, let's see what the problem is. Whoops, I'm sorry. I named that wrong. We're just trying to pass through any channel that is specified or none. So refresh. And the same is true for orderBy. So this is where it's up to you, of course. You could put it in here and just reach for the request directly here. And that would get rid of that. So if I refresh, I think this will work. And it does. Get rid of it. And we have normal links. And now we're favoring popularity. Yeah, you could do that. I don't love passing the request object around too much outside of the controller. I feel like the controller should be responsible for getting anything from the request and delegating. I'm happy to say it just doesn't matter in so many cases. But if we did want to keep that on, we could either just do this exact number, or we could just pass through a Boolean. So we're just going to let the query know.