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
03. Apply constraints to aggregates

Episodes

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

Transcript

00:00
The aggregate that we've looked at is a very, very simple scenario where a particular user has a relationship. There are no constraints to this. Now let's imagine that articles can be either published or unpublished.
00:15
Well in this case, this user is going to show all of the articles regardless of whether they're published or not. So what we're going to do in this episode is add a constraint to our articles, something in the database that tells us whether this article is published, and we're going to take
00:30
that into account when we actually create our aggregate. So to do this, the first thing I'm going to do is get rid of this alias. We can alias even if we're using a constraint here, so bear that in mind, and let's just bring this back to how it was before.
00:46
Great. Okay, so let's go and just add a column to our articles table. So let's go ahead and make a migration here, add published at to articles table, and let's go ahead and open that up, and let's go down and add this in as a timestamp, which I'd
01:08
always recommend. So published at, and let's just go down and fill in the down migration in case we need to roll this back, so let's just drop that column. And now that we've done this, let's go ahead and migrate, and yep, so we want to make sure
01:25
that this is nullable because, of course, it may not be published, so let's go ahead and do that. And there we go. Great.
01:33
All right. So we have published at set to null for all of these. Let's just say that one or two articles has been published, and let's save that out. Now the problem is with our aggregate, it's not taking into account this constraint at
01:48
all. It's just reading from the database. So we need to take this into account somewhere when we go ahead and set this alias or this aggregate up inside of here.
01:58
Okay, so first of all, let's come over to our article model, and we're going to go ahead and set our dates in here because this will now be a carbon instance when it comes back, so published at, and let's go down here and create a scope that we can use. So let's create out a scope called published, so only bring back published articles, and
02:21
let's bring our builder into here. I'm just going to put that as query for now just to save time. So let's say query and where not null published at. So if we were to use this scope when we were pulling articles out normally, we just come
02:38
over here and die dump on article, get, and then use published, that's going to bring back all articles that are published, e.g. the date has been filled, and we end up with only two items. So that's what we want within our aggregate.
02:56
How do we do that? Well, instead of defining this out like this, instead as an argument, we use an array. We still use articles, but then as the value for this key, we give a callback function. So I'm just going to pull find down just so it's a little bit easier to see, and let's
03:13
add in our callback function inside of here. So with this, what we can now do is we can bring in our query builder and we can constrain this. So even if we didn't over on the articles model have this scope already put in here,
03:27
we could just do this directly within there. Let's do that first of all. So I'm going to go ahead and say query, where not null, published at. So the change that we've made is passing an array in with the aggregate that we want to
03:37
fetch, but the value of this key or this array item is a callback function with a standard builder inside of it. Let's give this a refresh, and there we go. We have now got only two articles.
03:52
We take a look at the query. You can see that that scope has been applied and published at is not null. So we've got that in there, and that's pretty much it. Now, because we applied this to a scope, as a scope over here, it's a lot cleaner just
04:04
to now say query published. That's going to have exactly the same effect, but just looks a little bit cleaner in your code. And it's also cleaner than if you wanted to use a short function like this.
04:19
And there we go. So you can just pretty much do that all in one line as long as you have a nice short scope name and it makes sense. There we go.
04:26
Two articles. You can do anything in here with this query, whichever way that you need to constrain the count of the aggregate that you're pulling in, just pass this in as an array with the key in here and the actual query builder or thing that you need to build up.
04:44
Now once again, you can alias this. So let's just alias this to ABC and go ahead and die dump on user just to prove that we can do this. Let's give that a refresh.
04:53
Let's have a look, and there we go. ABC is now two. Of course, we don't want to do that, so let's get rid of that. Let's get rid of this, and we're back to our two articles that have been published.

Episode summary

In this episode, we dig into how to apply constraints when working with Eloquent aggregates. Previously, we looked at a simple case where a user just had articles, and we counted them all regardless of status. Now, we're taking it a step further by introducing the concept of published and unpublished articles.

We start by adding a published_at timestamp column to the articles table, making it nullable so unpublished articles won't have a value. Once that's set up, we update some articles to be published. The main issue we address is that our aggregate query counts all articles—for both published and unpublished ones. That's not what we want!

To solve this, we add a scope to our Article model to easily grab only published articles. Then, we update our aggregate query so it only counts articles where published_at is not null. We do this first inline as a callback right in the relation count, and then refactor it to use the published scope, which makes the code look a lot cleaner.

After that, we show that you can also alias your aggregate for convenience and demonstrate that this filtering works wherever you want to constrain your aggregate queries. In the end, you have a flexible way to constrain aggregates to fit pretty much any filter condition you need.

Episode discussion

No comments, yet. Be the first!