Criteria System: Champion Collection Filtering

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

Filtering a collection from inside of your entity like this is really convenient... but unless you know that you will always have a small number of total scientists... it's likely to slow down your page big.

Ready for a better way?! Introducing, Doctrine's Criteria system: a part of Doctrine that's so useful... and yet... I don't think anyone knows it exists!

Here's how it looks: create a $criteria variable set to Criteria::create():

            
            224 lines
            
            src/AppBundle/Entity/Genus.php
        
                                                    ... lines 1 - 5
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 7 - 15
                            
            class Genus
        
            {
        
                                                    ... lines 18 - 214
                            
                public function getExpertScientists()
        
                {
        
                    $criteria = Criteria::create()
        
                                                    ... lines 218 - 221
                            
                }
        
            }

Next, we'll chain off of this and build something that looks somewhat similar to a Doctrine query builder. Say, andWhere(), then Criteria::expr()->gt() for a greater than comparison. There are a ton of other methods for equals, less than and any other operator you can dream up. Inside gt, pass it 'yearsStudied', 20:

            
            224 lines
            
            src/AppBundle/Entity/Genus.php
        
                                                    ... lines 1 - 5
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 7 - 15
                            
            class Genus
        
            {
        
                                                    ... lines 18 - 214
                            
                public function getExpertScientists()
        
                {
        
                    $criteria = Criteria::create()
        
                        ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
        
                                                    ... lines 219 - 221
                            
                }
        
            }

And hey! Let's show off: add an orderBy() passing it an array with yearsStudied set to DESC:

            
            224 lines
            
            src/AppBundle/Entity/Genus.php
        
                                                    ... lines 1 - 5
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 7 - 15
                            
            class Genus
        
            {
        
                                                    ... lines 18 - 214
                            
                public function getExpertScientists()
        
                {
        
                    $criteria = Criteria::create()
        
                        ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
        
                        ->orderBy(['yearsStudied' => 'DESC']);
        
                                                    ... lines 220 - 221
                            
                }
        
            }

This Criteria describes how we want to filter. To use it, return $this->getGenusScientists()->matching() and pass that $criteria:

            
            224 lines
            
            src/AppBundle/Entity/Genus.php
        
                                                    ... lines 1 - 5
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 7 - 15
                            
            class Genus
        
            {
        
                                                    ... lines 18 - 214
                            
                public function getExpertScientists()
        
                {
        
                    $criteria = Criteria::create()
        
                        ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
        
                        ->orderBy(['yearsStudied' => 'DESC']);
        
                    return $this->getGenusScientists()->matching($criteria);
        
                }
        
            }

That is it!

Now check this out: when we go back and refresh, we get all the same results. But the queries are totally different. It still counts all the scientists for the first number. But then, instead of querying for all of the genus scientists, it uses a WHERE clause with yearsStudied > 20. It's now doing the filtering in the database instead of in PHP.

As a bonus, because we're simply counting the results, it ultimately makes a COUNT query. But if - in our template, for example - we wanted to loop over the experts, maybe to print their names, Doctrine would be smart enough to make a SELECT statement for that data, instead of a COUNT. But that SELECT would still have the WHERE clause that filters in the database.

In other words guys, the Criteria system kicks serious butt: we can filter a collection from anywhere, but do it efficiently. Congrats to Doctrine on this feature.

Organizing Criteria into your Repository

But, to keep my code organized, I prefer to have all of my query logic inside of repository classes, including Criteria. No worries! Open GenusRepository and create a new static public function createExpertCriteria():

            
            34 lines
            
            src/AppBundle/Repository/GenusRepository.php
        
                                                    ... lines 1 - 8
                            
            class GenusRepository extends EntityRepository
        
            {
        
                                                    ... lines 11 - 26
                            
                static public function createExpertCriteria()
        
                {
        
                                                    ... lines 29 - 31
                            
                }
        
            }

Tip

Whoops! It would be better to put this method in GenusScientistRepository, since it operates on that entity.

Copy the criteria line from genus, paste it here and return it. Oh, and be sure you type the "a" on Criteria and hit tab so that PhpStorm autocompletes the use statement:

            
            34 lines
            
            src/AppBundle/Repository/GenusRepository.php
        
                                                    ... lines 1 - 5
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 7 - 8
                            
            class GenusRepository extends EntityRepository
        
            {
        
                                                    ... lines 11 - 26
                            
                static public function createExpertCriteria()
        
                {
        
                    return Criteria::create()
        
                        ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
        
                        ->orderBy(['yearsStudied' => 'DESC']);
        
                }
        
            }

But wait, gasp! A static method! Why!? Well, it's because I need to be able to access it from my Genus class... and that's only possible if it's static. And also, I think it's fine: this method doesn't make a query, it simply returns a small, descriptive, static value object: the Criteria.

Back inside Genus, we can simplify things $this->getGenusScientists()->matching(GenusRepository::createExpertCriteria()):

            
            223 lines
            
            src/AppBundle/Entity/Genus.php
        
                                                    ... lines 1 - 16
                            
            class Genus
        
            {
        
                                                    ... lines 19 - 215
                            
                public function getExpertScientists()
        
                {
        
                    return $this->getGenusScientists()->matching(
        
                        GenusRepository::createExpertCriteria()
        
                    );
        
                }
        
            }

Refresh that! Sweet! It works just like before.

Criteria in Query Builder

Another advantage of building the Criteria inside of your repository is that you can use it in a query builder. Imagine that we needed to query for all of the experts in the entire system. To do that we could create a new public function - findAllExperts():

            
            45 lines
            
            src/AppBundle/Repository/GenusRepository.php
        
                                                    ... lines 1 - 8
                            
            class GenusRepository extends EntityRepository
        
            {
        
                                                    ... lines 11 - 29
                            
                public function findAllExperts()
        
                {
        
                                                    ... lines 32 - 35
                            
                }
        
                                                    ... lines 37 - 43
                            
            }

Tip

Once again, this method should actually live in GenusScientistRepository, but the idea is exactly the same :).

But, I want to avoid duplicating the query logic that we already have in the Criteria!

No worries! Just return $this->createQueryBuilder('genus') then, addCriteria(self::createExpertCriteria()):

            
            45 lines
            
            src/AppBundle/Repository/GenusRepository.php
        
                                                    ... lines 1 - 8
                            
            class GenusRepository extends EntityRepository
        
            {
        
                                                    ... lines 11 - 29
                            
                public function findAllExperts()
        
                {
        
                    return $this->createQueryBuilder('genus')
        
                        ->addCriteria(self::createExpertCriteria())
        
                                                    ... lines 34 - 35
                            
                }
        
                                                    ... lines 37 - 43
                            
            }

Finish with the normal getQuery() and execute():

            
            45 lines
            
            src/AppBundle/Repository/GenusRepository.php
        
                                                    ... lines 1 - 8
                            
            class GenusRepository extends EntityRepository
        
            {
        
                                                    ... lines 11 - 26
                            
                /**
        
                 * @return Genus[]
        
                 */
        
                public function findAllExperts()
        
                {
        
                    return $this->createQueryBuilder('genus')
        
                        ->addCriteria(self::createExpertCriteria())
        
                        ->getQuery()
        
                        ->execute();
        
                }
        
                                                    ... lines 37 - 43
                            
            }

How cool is that!?

Ok guys, that's it - that's everything. We just attacked the stuff that really frustrates people with Doctrine and Forms. Collections are hard, but if you understand the mapping and the inverse side reality, you write your code to update the mapping side from the inverse side, and understand a few things like orphanRemoval and cascade, everything falls into place.

Now that you guys know what to do, go forth, attack collections and create something amazing.

All right guys, see you next time.

Leave a comment!

Maxim M. | posted 2 years ago | edited

Big thanks!

One problem:
Invalid: ->orderBy(['yearsStudied', 'DESC'])
Valid ->orderBy(['yearsStudied' => 'DESC'])

Information from official repository Doctrine Collections: <a href="https://github.com/doctrine/collections/blame/5f0470363ff042d0057006ae7acabc5d7b5252d5/lib/Doctrine/Common/Collections/Criteria.php#L154">Link to github</a>

1 Reply

MolloKhan | SFCASTS | Maxim M. | posted 2 years ago | edited

Hey Maxim M.

I don't fully get it. In the link you posted I see that the `orderBy` method accepts an array, and about your second example (the valid one) I think it won't even compile (unless Disqus messed up with your syntax)

Cheers!

Maxim M. | MolloKhan | posted 2 years ago

I made a typo. Already changed the original post.

MolloKhan | SFCASTS | Maxim M. | posted 2 years ago | edited

Hey Maxim M.

Sorry for my slow reply, SymfonyWorld week was crazy. I see now your point. We'll add a note about it

Cheers!

Mike P. | posted 3 years ago

I have a n+1 problem, that why I need to join to another table.
I could create a new Method inside my repository and join the other table to solve it.

But the correct and more elegant way would be to use criteria to do this (Because I have the benefit of calling my Action inside my entity and don't have to write any Controller code)

But how the heck can I write ->join() with the Criteria system? I haven't found a solution.
Its really simple with the query builder, but is it possible with the Criteria system as well and how?

Thierry R. | Mike P. | posted 2 years ago | edited

You could use filter instead of matching eg inside a Contrat Entity, and even combine them :

`
public function getActiveContrat(): Collection
{
$criteria = Criteria::create()

 ->andWhere(
   Criteria::expr()->eq('bar', false)
 )
 ->orderBy(['foo' => 'DESC'])
 ->setMaxResults(1);

$sub = 'Baz';

return $this->contrats

 ->filter(function($key, $element) use ($sub) {
   return
     $key->getBaseType()->getNom() !== $sub
     &&
     (
       $key->getDateFoo() > new \DateTime('NOW') ||
       $key->getDateBAr() === null
     )
   ;
 })

->matching($criteria);
}
`

weaverryan | SFCASTS | Mike P. | posted 3 years ago | edited

Hey Mike P.!

I can't remember for sure, but I *think* I checked into that a bit when I wrote this tutorial and I *think* it's not possible. In other words, I think you'll need to do a repository method for this :/.

Sorry I didn't have better news!

Cheers!

Mike P. | weaverryan | posted 3 years ago

Thank you very much for your reply Ryan!
Is it correct as well, that with Criteria() we can't do custom querys like SELECT AVG() or SELECT SUM()?

I can't find any possibility via google or in the source code.

weaverryan | SFCASTS | Mike P. | posted 3 years ago | edited

Hey Mike P.!

Criteria are such a strange, little-known, little-documented feature :p. I believe you are correct. I've never tried it, but I believe that this system is all about *filtering* and re-ordering results, but not controlling which fields (or a SUM for example) are included. It's a shame - because I'm sure all these things are possible to implement in that system - but someone needs to do it... and Doctrine is complex.

Cheers!

Rsteuber | posted 3 years ago

I have a question about de criteria filter on collection. When i use an criteria betweenDates and have a second criteria with byPerson does doctrine filter first the betweenDate and then byPerson? This because of performances.

Or is it better to use a querybuilder with the given dates. parse it trough an arrayCollection and then use the criteria byPerson?

I'm Courious ..

Cheers Rob

MolloKhan | SFCASTS | Rsteuber | posted 3 years ago | edited

Hey Rsteuber

That's a good question. A criteria it's only a way to add more statements to your query, in this case more clauses to the WHERE. So, I think it relies on the DB engine you are using (MySql, Postgres, etc). What you can do is to execute your code and watch the final ran query in the profiler

Cheers!

Luc Hamers | posted 5 years ago

You helped me a lot with this series on many2many relationsships, thanks a lot!

I have one question about the criteria filtering. Would it be possible to order the genus experts in the getExpertScientists method by a field in the user table? I now have a special query builder in the repository class, but I am not sure, if this works with the form system because it (as far as I understand it) would use the getExpertScientists in the genus class, right?

weaverryan | SFCASTS | Luc Hamers | posted 5 years ago | edited

Hey Luc!

Awesome - very happy to hear things make more sense now!

Now, about your question :). First, yes, if you're using the CollectionType like we do in this tutorial, then the form system will call getExpertScientists in order to know which embedded forms to list on the page (assuming your form field is called expertScientists). Unfortunately, it looks like the Criteria system does not support joining :(. This is definitely a bummer - there's an issue about it here: https://github.com/doctrine/doctrine2/issues/2918. So, the only way I can think to do this is in the old, inefficient way - e.g. in getExpertScientists, loop over all scientists and manually create an array with only the ones you want. This will work fine, unless you have a HUGE list of scientists. If you DO have a huge list, then you would need to do something a bit more clever/ugly, like setting the mapped option to false on that field, and manually setting the data on it in the controller $form['expertScientists']->setData($filteredExpertScientists).

Let me know if that helps! Cheers!

Luc Hamers | weaverryan | posted 5 years ago

Hello Ryan,

Thank you for the tip, its works great with an iterator and uasort in the getExpertScientists (well, in my version returning phone numbers). It is OK in this situation to do it in the getter, since a user only has a hand-full of numbers. But it is good to know that it could also work for larger arrays with the mapped-option.

weaverryan | SFCASTS | Luc Hamers | posted 5 years ago

Awesome! And great question :)

somecallmetim | posted 5 years ago

Thrilled I got through the series! It was fantastic and instrumental to my building my own Symfony web app. That being said, I'm surprised we got all the way to the end and the tutorial app isn't really finished. Are there going to be more tutorials based on this project?

weaverryan | SFCASTS | somecallmetim | posted 5 years ago

Woohoo! Congrats! I understand what you mean - the app won't ever really be finished - when we write the tutorials, we emphasize teaching the important stuff... not necessarily working on all the small, less-important details to get the app fully ready. And then, we also keep adding onto it as more important topic become obvious :). Right now, we don't have anything immediately planned for this tutorial series. However, in the future, we do want to cover deployment, HTTP caching, unit testing, file upload, and a potentially a bunch of other topics. If there's something you feel is missing after going through everything, I'd be eager to know it!

Cheers!

Benutzer | posted 5 years ago

I like the idea having criterias mapping to one "business rule" to select different subsets of my entities.
But using this with more that one entity leads to an error I described here:
https://stackoverflow.com/q...

Maybe someone has done this before or can give me a better solution to this.

MolloKhan | SFCASTS | Benutzer | posted 5 years ago | edited

Hey Benutzer

Nice question! There you have an answer in SO. Ff you want to choose the Criteria's way, you will have to provide an alias. it can be a constant living in the Owner Repository class, but you will have to pass it every time

Another thing you can do is, only use a Criteria for your entities and use the QueryBuilder in your repositories instead. This might look as code duplication, but you are working in two separate layers. The QueryBuilder works at the DataBase layer, modifying the query directly, and your entities works with Collections. So, I think this duplication is a good thing, because it gives you more flexibility.

Cheers!

2 Reply

Giorgio Pagnoni | posted 5 years ago | edited

As soon as I switched to Doctrine's Criteria system I started getting this error:


An exception has been thrown during the rendering of a template ("Notice: Undefined property: AppBundle\Entity\GenusScientist::$1") in 
genus/list.html.twig at line 20.

Victor | SFCASTS | Giorgio Pagnoni | posted 5 years ago

Hey Giorgio,

Could you show us the line 20 in your "genus/list.html.twig" template? And the code where you use those Doctrine Criteria which caused this error?

Cheers!

Giorgio Pagnoni | Victor | posted 5 years ago | edited

Here it is! Should have posted it with my comment now that I think about it.

Twig:`
{{ genus.genusScientists|length }} ({{ genus.expertScientists|length }} experts)`

Php:



public function getExpertScientists(){
  $criteria = Criteria::create()
    ->andWhere(Criteria::expr()->gt('yearsStudied', 20))
    ->orderBy(['yearsStudied', 'DESC']);
  return $this->getGenusScientists()->matching($criteria);
}

Thank you!

unicolored | Giorgio Pagnoni | posted 4 years ago

Hi from 2018, Symfony 4.1.4.
I had a similar error using Criteria and i fix the array to make it work:

->orderBy(['yearsStudied' => 'DESC']);

1 Reply

Pipo | Giorgio Pagnoni | posted 5 years ago

Hi Giorgio,
Try to remove the ->orderBy() section to see the error disappear.

Regards,

Victor | SFCASTS | Giorgio Pagnoni | posted 5 years ago

Hey Giorgio,

Hm, I don't any problems with your code, actually, it's exactly the same code as we have in screencast. I wonder, what if you just return "$this->getGenusScientists()" in this "getExpertScientists()" method. Does it fix the error? Because I suppose the problem in another place and the error should remain.

Cheers!

This course is built on Symfony 3, but most of the concepts apply just fine to newer versions of Symfony.

Symfony 3

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "symfony/symfony": "3.4.*", // v3.4.49
        "doctrine/orm": "^2.5", // 2.7.5
        "doctrine/doctrine-bundle": "^1.6", // 1.12.13
        "doctrine/doctrine-cache-bundle": "^1.2", // 1.4.0
        "symfony/swiftmailer-bundle": "^2.3", // v2.6.7
        "symfony/monolog-bundle": "^2.8", // v2.12.1
        "symfony/polyfill-apcu": "^1.0", // v1.23.0
        "sensio/distribution-bundle": "^5.0", // v5.0.25
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.29
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.4
        "composer/package-versions-deprecated": "^1.11", // 1.11.99.4
        "knplabs/knp-markdown-bundle": "^1.4", // 1.9.0
        "doctrine/doctrine-migrations-bundle": "^1.1", // v1.3.2
        "stof/doctrine-extensions-bundle": "^1.2" // v1.3.0
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.1.7
        "symfony/phpunit-bridge": "^3.0", // v3.4.47
        "nelmio/alice": "^2.1", // v2.3.6
        "doctrine/doctrine-fixtures-bundle": "^2.3" // v2.4.1
    }
}

Edit on GitHub

TRACK

Symfony 3 >

COURSE

Doctrine Collections: ManyToMany, Forms & other Complex Relations

Criteria System: Champion Collection Filtering

Keep on Learning!

Organizing Criteria into your Repository

Criteria in Query Builder

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 5
	use Doctrine\Common\Collections\Criteria;
	... lines 7 - 15
	class Genus
	{
	... lines 18 - 214
	public function getExpertScientists()
	{
	$criteria = Criteria::create()
	... lines 218 - 221
	}
	}

	... lines 1 - 8
	class GenusRepository extends EntityRepository
	{
	... lines 11 - 26
	static public function createExpertCriteria()
	{
	... lines 29 - 31
	}
	}

	... lines 1 - 5
	use Doctrine\Common\Collections\Criteria;
	... lines 7 - 8
	class GenusRepository extends EntityRepository
	{
	... lines 11 - 26
	static public function createExpertCriteria()
	{
	return Criteria::create()
	->andWhere(Criteria::expr()->gt('yearsStudied', 20))
	->orderBy(['yearsStudied' => 'DESC']);
	}
	}

	... lines 1 - 16
	class Genus
	{
	... lines 19 - 215
	public function getExpertScientists()
	{
	return $this->getGenusScientists()->matching(
	GenusRepository::createExpertCriteria()
	);
	}
	}

	... lines 1 - 8
	class GenusRepository extends EntityRepository
	{
	... lines 11 - 29
	public function findAllExperts()
	{
	... lines 32 - 35
	}
	... lines 37 - 43
	}