> Symfony 6 > Go Pro with Doctrine Queries
Login to bookmark this video
Buy Access to Course
12.

Criteria: Filter Relation Collections

|

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

On the category show page, we're looping over all the fortune cookies in that category. Let's check out the template: templates/fortune/showCategory.html.twig. Here it is: we loop over category.fortuneCookies and render some stuff.

40 lines | templates/fortune/showCategory.html.twig
// ... lines 1 - 20
{% for fortuneCookie in category.fortuneCookies %}
<tr class="hover:bg-slate-200">
<td class="border p-4">
{{ fortuneCookie.fortune }}
</td>
<td class="border p-4">
{{ fortuneCookie.numberPrinted }} printed since {{ fortuneCookie.createdAt|date('M jS Y') }}
</td>
</tr>
{% endfor %}
// ... lines 31 - 40

But... there's a problem. Open up the FortuneCookie entity. It has a bool $discontinued flag. Occasionally, we have to stop producing a specific fortune cookie... for one reason or another. Like the time we had a fortune cookie that said "You will be happy... until you realize reality is an illusion". That one slipped past quality control. When this happens, we set discontinued to true.

At the moment, we're looping over all the fortune cookies for a category: including both current and discontinued cookies! But management is really only interested in current fortune cookies. We need a way to hide the discontinued ones. How can we do that?

Over in the controller for this page - FortuneController - we could create a separate query from the $fortuneCookieRepository with

WHERE category = :category and discontinued = false.

But... that's lame! Looping over category.fortuneCookies is so easy! Do we really need to back up to the controller, create a custom query and pass in the results as a new Twig variable? Couldn't we somehow use the category object... but filter out the discontinued cookies! Absolutely! And if we do it correctly, we can do it really efficiently.

The first step is optional, but in the controller, change ->findWithFortunesJoin() back to just ->find(). I'm doing this - which removes the join - just so that it's a easier to see the end result of what we're about to do.

47 lines | src/Controller/FortuneController.php
// ... lines 1 - 12
class FortuneController extends AbstractController
{
// ... lines 15 - 30
public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
{
$category = $categoryRepository->find($id);
// ... lines 34 - 44
}
}

Doing this doesn't change the page... except that our queries go up to three. That's one query for the Category, our custom query that we're making, and then one query for all the fortunes inside of this Category.

Adding a Custom Entity Method for Discontinued Cookies

Remember the goal: we want to be able to call something on the Category object to get back the related fortune cookies... but hiding the discontinued ones.

Open up the Category entity and find getFortuneCookies(). There it is. Below, add a new method called getFortuneCookiesStillInProduction(). This, like the normal method, will return a Doctrine Collection. And... just to help my editor, copy the @return doc above to say that this is a Collection of FortuneCookie objects.

99 lines | src/Entity/Category.php
// ... lines 1 - 10
class Category
{
// ... lines 13 - 60
/**
* @return Collection<int, FortuneCookie>
*/
public function getFortuneCookiesStillInProduction(): Collection
{
}
// ... lines 68 - 97
}

So... what do we do inside? We could loop over $this->fortuneCookies as $fortuneCookie and create an array of objects that are not discontinued. Easy!

But... as soon as we start working with $this->getFortuneCookies(), that will cause Doctrine to query for every related fortune cookie. Do you see the problem? We might be asking Doctrine to query and prepare 100 FortuneCookie objects... even though this final $inProduction collection may only contain 10 of them. What a waste!

What we really want to do is tell Doctrine that when it makes the query for the related fortune cookies, it should add an extra WHERE discontinued = false to that query.

Hello Criteria

But... how the heck do we do that? Doctrine makes that query automatically and... magically somewhere in the background. Whelp, this is where the criteria system comes in handy.

It works like this: $criteria = Criteria:: - the one from Doctrine\Common\Collections - create().

103 lines | src/Entity/Category.php
// ... lines 1 - 7
use Doctrine\Common\Collections\Criteria;
// ... lines 9 - 11
class Category
{
// ... lines 14 - 64
public function getFortuneCookiesStillInProduction(): Collection
{
$criteria = Criteria::create()
// ... lines 68 - 70
}
// ... lines 72 - 101
}

This object is a bit like the QueryBuilder, but not exactly the same. We can say ->andWhere() and then use Criteria:: again with expr()->. This expr() or "expression" lets us, sort of, build the WHERE clause. It has methods like in, contains or gt for "greater than". We want eq() for "equals". Inside, say discontinued, false.

103 lines | src/Entity/Category.php
// ... lines 1 - 64
public function getFortuneCookiesStillInProduction(): Collection
{
$criteria = Criteria::create()
->andWhere(Criteria::expr()->eq('discontinued', false));
// ... lines 69 - 70
}
// ... lines 72 - 103

Ok, this, by itself, just creates an object that "describes" a WHERE clause that could be added to some other query. To use it, return $this->fortuneCookies->matching($criteria).

103 lines | src/Entity/Category.php
// ... lines 1 - 64
public function getFortuneCookiesStillInProduction(): Collection
{
// ... lines 67 - 69
return $this->fortuneCookies->matching($criteria);
}
// ... lines 72 - 103

Cool, huh? We're saying:

Hey Doctrine! Take this collection, but only return the ones that match this criteria.

And as we'll see in a minute, this will modify the query to get those fortune cookies!

To use this method, over in showCategory.html.twig, replace the category.fortuneCookies loop with category.fortuneCookiesStillInProduction.

40 lines | templates/fortune/showCategory.html.twig
// ... lines 1 - 2
{% block body %}
// ... lines 4 - 20
{% for fortuneCookie in category.fortuneCookiesStillInProduction %}
// ... lines 22 - 29
{% endfor %}
// ... lines 31 - 38
{% endblock %}

Let's do this! Refresh, and... I don't actually know if any of these are discontinued, but it did go from three to two! And the best part? Check out that query! Here's the first one for the category, here's our custom one... but take a look at this last query. When we ask for the "fortune cookies still in production", it queries from fortune_cookie, where the category = our category and where t0.discontinued is false! So it made the most efficient query to fetch just the fortune cookies that we need. That's amazing.

Organizing your Criteria Code in the Repository

Now, one minor downside is that... I normally like to keep my query logic inside a repository... not in the middle of an entity. Fortunately, we can move it there.

Because this deals with fortune cookies, open FortuneCookieRepository and, anywhere, add a new public static function called... how about createFortuneCookiesStillInProductionCriteria(). This will return a Criteria object.

Now, grab the $criteria statement from the entity... and return that.

102 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 8
use Doctrine\Common\Collections\Criteria;
// ... lines 10 - 19
class FortuneCookieRepository extends ServiceEntityRepository
{
// ... lines 22 - 26
public static function createFortuneCookiesStillInProductionCriteria(): Criteria
{
return Criteria::create()
->andWhere(Criteria::expr()->eq('discontinued', false));
}
// ... lines 32 - 100
}

The Method is Static?

And yes, this is a static method... which I don't use too often. There are two reasons for this. First, these Criteria objects aren't actually making queries... and they don't rely on any data or services. And so, this method can be static. Second, and more importantly, we don't have access to the repository object from inside Category. So... if we want to call a method on a repository, it needs to be static. This is a special thing I typically do in my repositories only for this criteria situation.

Back in the entity, say $criteria equals FortuneCookieRepository::createFortuneCookiesStillInProductionCriteria().

103 lines | src/Entity/Category.php
// ... lines 1 - 5
use App\Repository\FortuneCookieRepository;
// ... lines 7 - 12
class Category
{
// ... lines 15 - 65
public function getFortuneCookiesStillInProduction(): Collection
{
$criteria = FortuneCookieRepository::createFortuneCookiesStillInProductionCriteria();
return $this->fortuneCookies->matching($criteria);
}
// ... lines 72 - 101
}

Logic centralization, check! Oh, and we can even reuse these Criteria objects inside a QueryBuilder. Let's see... I don't have a good example... so... in this method, above, let's pretend I'm creating a QueryBuilder with $this->createQueryBuilder('fortune_cookie'). To add the criteria it's... ->addCriteria(self::createFortuneCookiesStillInProduction()).

So, even though the criteria system is a bit different from the normal QueryBuilder, we can still reuse them everywhere. Oh, and let's check that things are still working. We're good!

Using the Criteria System in the Controller + EXTRA_LAZY Fetch

On the homepage, we have a similar problem. This says "Proverbs(3)", but if we click that, there are two. What's happening here? Over in homepage.html.twig... let's see... ah, yes. We're looping over categories, and then calling category.fortuneCookies|length which, as we know, returns all the fortune cookies. Change that to fortuneCookiesStillInProduction.

17 lines | templates/fortune/homepage.html.twig
// ... lines 1 - 2
{% block body %}
// ... lines 4 - 7
{% for category in categories %}
// ... line 9
<span class="fa {{ category.iconKey }}"></span> <span class="font-bold text-lg">{{ category.name }}</span> ({{ category.fortuneCookiesStillInProduction|length }})
// ... lines 11 - 13
{% endfor %}
// ... line 15
{% endblock %}

Back on the homepage, watch this "(3)". It should go down to 2, and... it does. But that's not even the best part. Open up the query for that. Remember, thanks to our fetch EXTRA_LAZY, because we're only counting the number of fortune cookies, it knows to make a super-fast COUNT query. And thanks to the criteria system, it's selecting COUNT FROM fortune_cookies WHERE the category = our category and discontinued = false. Wow!

Next: We want to hide discontinued fortune cookies from everywhere on our site. Is there a way that we could hook into Doctrine and add that WHERE clause automatically... everywhere? There is. It's called filters.