This episode is for members only

Sign up to access "Eloquent Relationships By Example" right now.

Get started
Already a member? Sign in to continue
Playing
33. Using aggregate functions

Episodes

0%
Your progress
  • Total: 4h 18m
  • Played: 0m
  • Remaining: 4h 18m
Join or sign in to track your progress

Transcript

00:00
With the has one of many relationship, we can also use aggregate functions. What does that mean?
00:05
Well, it means that we can get the host, in this case, with the most amount of, say, upvotes or likes or whatever. So in this episode, we're going to create out a new endpoint, which just dumps out the user's most voted
00:21
for post ever, regardless of the discussions that we've created. So let's go ahead and start out by creating out a migration in here called add.
00:31
And let's just keep this at votes to the post table. And let's go ahead and open add votes to post table. We'll just add a really simple integer in here, which increments maybe within your UI
00:45
how many votes a particular post has got. So we'll create that as an integer, and we'll say votes. And we'll go ahead and drop this on our down migration if we roll anything back.
00:57
So let's go ahead and say PHP artisan migrate. And that's not working at the moment just because we don't have this set to nullable. So let's set a default of 0 on there.
01:08
OK, let's come over to our posts table. And sure enough, we have our votes in there now. Let's go and just for the sake of testing this, because I'm going to test this with just one user,
01:18
set every single post in here to this user here. And let's go ahead and add in some vote count. So I'll say one for this, five, 100, and maybe we'll set this one to zero.
01:30
So our goal now is to create a page in our application, or this could be absolutely anywhere, within a profile section or something, where it shows just our most voted for posts
01:42
just to kind of show it. So we're going to go over and create out a route in here. And let's do this down here. And we'll just say most voted.
01:56
Create our closure. And we'll grab that user, like so. And we'll find that by their ID. I think it's two, just double check.
02:07
Yeah, great. OK, so we've got that user. Now, the whole goal again around this that we've already spoken about in the last episode
02:15
is we don't want to do something like this. We don't want to say something like user and then post, which we don't even have set up at the moment, and then say something like order by votes
02:27
in ascending order, or in this case, descending order, and then grab the latest. We don't want to be doing stuff like that. Once again, a user could have thousands of posts,
02:38
and this would be a huge waste of memory. Loading these posts in and even doing this at a database level and putting the query at a database level is not very efficient.
02:49
So we're going to go ahead and, first of all, set up a relationship on our user model for posts because we know that a user has many posts. We're not going to use that directly, but that
02:59
could be useful elsewhere. So we're going to say has many and posts. So now you can, say, on a user's profile page, get a list of their posts.
03:07
But now we want the most upvoted post. So let's create our method called most voted post. And let's go ahead and return this relationship. So it's exactly the same thing.
03:20
It has one because we know that there's only ever going to be one most upvoted post. So we're going to go ahead and provide the post fully qualified namespace in there.
03:30
And then down here, instead of saying something like latest of many, which doesn't really make sense because that will just grab the latest post for the user, we're going to go ahead and say of many.
03:41
Then the first argument that we use here is the column, which is going to be the votes. So it's that new votes column we just added. And then we're going to provide an SQL aggregate function,
03:52
which could be max, could be min. So we're going to go ahead and say max, because we want to find the post that has the most, e.g. the max, votes inside of here.
04:03
So let's go ahead and die dump on our most voted post and see what we get. So let's go ahead and die dump on user. Most voted post, remember we don't
04:11
need to access this method. It is now a relationship type, which just returns to us one model, which we know is a post. So let's go over and check this out.
04:19
So most, let's just see what we call that, most voted. And sure enough, we have the user's most upvoted post. So this is obviously in the context of a forum with discussions and posts, but this
04:35
could be absolutely anything. For example, it could be the most expensive item or could be really just any column with the highest amount of something.
04:43
OK, so in our practical example, as well as getting all of the discussions and the latest post for them discussions just with one very simple relationship rather than having to mess around with collections,
04:55
we have also now looked at these aggregate functions, which could be really helpful when you just need to conveniently pluck something out based on a max or a min value.

Episode summary

In this episode, we dive into how you can use aggregate functions in your Eloquent relationships. Specifically, we look at how to get a user's most upvoted (or most 'voted for') post with a neat and efficient query—no messy collection manipulation required!

We start by adding a new votes column to our posts table and updating some test data. After that, we move on to setting up a new route and creating a practice endpoint that fetches and displays the user's top-voted post. Instead of looping through posts or writing manual queries, we take advantage of Eloquent's has one of many relationship with aggregated functions (like max() for the highest number of votes).

You'll learn how to set up a custom relationship on the User model using the ofMany method. We use max as the aggregate function and show that this approach is both memory efficient and very readable. To finish up, you'll see how this technique can be adapted to get other extremes, such as the most expensive item, or really anything based on a min/max column value.

By the end, you'll understand how to fetch "the one of many" using aggregate functions, making your code simpler and faster!

Episode discussion

No comments, yet. Be the first!