Laravel Aggregates
This episode is for members only

Sign up to access "Laravel Aggregates" right now.

Get started
Already a member? Sign in to continue
Playing
06. Summing up columns

Episodes

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

Transcript

00:00
So far for aggregates, we've only looked at the count aggregate,
00:04
but there are others. We have aggregates like the sum of certain things or the min or max of things. Now, I'll let you go ahead and use them as you need.
00:15
They're all in the documentation. But we're going to take a quick look at sum because I think this is probably going to be the most used. OK, so what do we want to sum up?
00:24
Well, let's think of a fake scenario here. Let's say that comments or articles had votes on them. What we would want to do is sum up how many votes had been placed on them, articles or comments.
00:39
So let's go ahead and do this for articles. And for this, we're just going to need to go ahead and add another column to the database. So let's go ahead and say make migration,
00:48
add votes to articles table. And again, this is going to be a really simple implementation. So add votes to articles table. Let's go down and just add in an integer in here.
01:02
And of course, we're going to call that votes. OK, let's go ahead and run phpArtisanMigrate. And let's look at this. And yeah, of course, once again,
01:10
this either needs a default or it needs to be nullable. We'll set a default of zero. Let's run that again. Great.
01:17
OK, so over in our articles table, let's just add a couple of votes to some of these just so we can play around. So let's save the changes.
01:26
So what we want to do is rather than grab a collection back of articles and then sum these up within the collection, once again, we want to do this at the database level. And this applies to any of the aggregate types
01:38
that Laravel builds up for you. So we want to sum all of these up. Let's come over to routes and web. And with this, we can just continue chaining this on.
01:46
We don't need to create any kind of separate query for this. We can do this all in one query. So we're going to now instead say with sum. And this takes two arguments.
01:56
So let's go ahead and choose the table that we want to sum or the relationship in this case that we want to sum and then the column that we want to sum up, which is going to be votes.
02:07
So that's going to give us an articles votes sum. If we give this a refresh, we don't see any difference apart from the fact that the query has now changed to include a sum of, if we look here,
02:21
the votes as articles underscore sum underscore votes. And of course, we can check this out by dying and dumping on the user model that we're getting back.
02:30
Give that a refresh. And there we go. We've got 28. So then random numbers that we put in
02:37
all add up or sum up to 28. So now what we can do is also go ahead and include this in our template. So let's go and just put this at the top next to articles.
02:48
And let's just take a look at the column name, paste this in, paste this in. And we will say vote. And we'll just put article vote or article votes in there.
03:02
Let's go over, give this a refresh. And there we go. 28 article votes. Now, of course, we can also alias these.
03:08
So if we wanted to call this something else like article votes count, for example, if that made more sense to you, we can do that. Switch this over here and here.
03:20
Give that a refresh. We get exactly the same thing. It's just alias this in a slightly different way for us within the query.

Episode summary

In this episode, we're diving deeper into aggregate functions, specifically focusing on how to sum up columns in your database using Laravel. Previously, we looked at the count aggregate, but now we're exploring other useful aggregates like sum, min, and max. The star of the show for most apps is probably going to be sum, so that's what we zero in on.

We walk through a fictional scenario where articles (or comments) get votes. To enable summing votes, we start by editing the articles table: we run a migration to add a votes column and set it up with a default value. Then, we seed some data into the articles to play around with.

You’ll see how to perform the sum directly with a database query (instead of summing things up in a PHP collection), keeping things efficient. We use Laravel's query builder methods to sum up the votes for articles in just a single database call. The episode shows step-by-step how to update your code, run the migrations, and even display the result in your view.

Plus, there’s a quick tip on how you can alias the resulting sum in your queries, so it's named however makes the most sense for your project.

By the end of the video, you'll be good to go with summing up columns using Laravel—whether it's for votes, sales, points, or anything else that needs totaling!

Episode discussion

No comments, yet. Be the first!