Buy
Buy

Collection Magic with Criteria

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

Login Subscribe

Of course, if we wanted to remove any of the deleted comments from this collection, we could loop over all of the comments, check if each is deleted, and return an array of only the ones left. Heck, the collection object even has a filter() method to make this easier!

But... there's a problem. If we did this, Doctrine would query for all of the comments, even though we don't need all of the comments. If your collection is pretty small, no big deal: querying for a few extra comments is probably fine. But if you have a large collection, like 200 comments, and you want to return a small sub-set, like only 10 of them, that would be super, super wasteful!

Hello Criteria

To solve this, Doctrine has a super powerful and amazing feature... and yet, somehow, almost nobody knows about it! Time to change that! Once you're an expert on this feature, it'll be your job to tell the world!

The system is called "Criteria". Instead of looping over all the data, add $criteria = Criteria - the one from Doctrine - Criteria::create():

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 14
class Article
{
... lines 17 - 183
public function getNonDeletedComments(): Collection
{
$criteria = Criteria::create()
... lines 187 - 191
}
... lines 193 - 215
}

Then, you can chain off of this. The Criteria object is similar to the QueryBuilder we're used to, but with a slightly different, well, slightly more confusing syntax. Add andWhere(), but instead of a string, use Criteria::expr(). Then, there are a bunch of methods to help create the where clause, like eq() for equals, gt() for greater than, gte() for greater than or equal, and so on. It's a little object-oriented builder for the WHERE expression.

In this case, we need eq() so we can say that isDeleted equals false:

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 14
class Article
{
... lines 17 - 183
public function getNonDeletedComments(): Collection
{
$criteria = Criteria::create()
->andWhere(Criteria::expr()->eq('isDeleted', false))
... lines 188 - 191
}
... lines 193 - 215
}

Then, add orderBy, with createdAt => 'DESC' to keep the sorting we want:

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 14
class Article
{
... lines 17 - 183
public function getNonDeletedComments(): Collection
{
$criteria = Criteria::create()
->andWhere(Criteria::expr()->eq('isDeleted', false))
->orderBy(['createdAt' => 'DESC'])
;
... lines 190 - 191
}
... lines 193 - 215
}

Creating the Criteria object doesn't actually do anything yet - it's like creating a query builder. But now we can say return $this->comments->matching() and pass $criteria:

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 14
class Article
{
... lines 17 - 183
public function getNonDeletedComments(): Collection
{
$criteria = Criteria::create()
->andWhere(Criteria::expr()->eq('isDeleted', false))
->orderBy(['createdAt' => 'DESC'])
;
return $this->comments->matching($criteria);
}
... lines 193 - 215
}

Because, remember, even though we think of the $comments property as an array, it's not! This Collection return type is an interface from Doctrine, and our property will always be some object that implements that. That's a long way of saying that, while the $comments property will look and feel like an array, it is actually an object that has some extra helper methods on it.

The Super-Intelligent Criteria Queries

Anyways, ready to try this? Move over and refresh. Check it out: the 8 comments went down to 7! And the deleted comment is gone. But you haven't seen the best part yet! Click to open the profiler for Doctrine. Check out the last query: it's perfect. It no longer queries for all of the comments for this article. Nope, instead, Doctrine executed a super-smart query that finds all comments where the article matches this article and where isDeleted is false, or zero. It even did the same for the count query!

Doctrine, that's crazy cool! So, by using Criteria, we get super efficient filtering. Of course, it's not always necessary. You could just loop over all of the comments and filter manually. If you are removing only a small percentage of the results, the performance difference is minor. The Criteria system is better than manually filtering, but, remember! Do not prematurely optimize. Get your app to production, then check for issues. But if you have a big collection and need to return only a small number of results, you should use Criteria immediately.

Organizing the Criteria into the Repository

One thing I don't like about the Criteria system is that I do not like having query logic inside my entity. And this is important! To keep my app sane, I want to have 100% of my query logic inside my repository. No worries: we can move it there!

In ArticleRepository, create a public static function called createNonDeletedCriteria() that will return a Criteria object:

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 15
class CommentRepository extends ServiceEntityRepository
{
... lines 18 - 22
public static function createNonDeletedCriteria(): Criteria
{
... lines 25 - 28
}
... lines 30 - 58
}

In Article, copy the Criteria code, paste it here, and return:

... lines 1 - 6
use Doctrine\Common\Collections\Criteria;
... lines 8 - 15
class CommentRepository extends ServiceEntityRepository
{
... lines 18 - 22
public static function createNonDeletedCriteria(): Criteria
{
return Criteria::create()
->andWhere(Criteria::expr()->eq('isDeleted', false))
->orderBy(['createdAt' => 'DESC'])
;
}
... lines 30 - 58
}

These are the only static methods that you should ever have in your repository. It needs to be static simply so that we can use it from inside Article. That's because entity classes don't have access to services.

Use it with $criteria = ArticleRepository::createNonDeletedCriteria():

... lines 1 - 4
use App\Repository\CommentRepository;
... lines 6 - 15
class Article
{
... lines 18 - 184
public function getNonDeletedComments(): Collection
{
$criteria = CommentRepository::createNonDeletedCriteria();
return $this->comments->matching($criteria);
}
... lines 191 - 213
}

Side note: we could have also put this method into the CommentRepository. When you start working with related entities, sometimes, it's not clear exactly which repository class should hold some logic. No worries: do your best and don't over-think it. You can always move code around later.

Ok, go back to your browser, close the profiler and, refresh. Awesome: it still works great!

Using the Criteria in a QueryBuilder

Oh, and bonus! in ArticleRepository, what if in the future, we need to create a QueryBuilder and want to re-use the logic from the Criteria? Is that possible? Totally! Just use ->addCriteria() then, in this case, self::createNonDeletedCriteria():

class ArticleRepository extends ServiceEntityRepository
{
    public function findAllPublishedOrderedByNewest()
    {
        $this->createQueryBuilder('a')
            ->addCriteria(CommentRepository::createNonDeletedCriteria());

        return $this->addIsPublishedQueryBuilder()
            ->orderBy('a.publishedAt', 'DESC')
            ->getQuery()
            ->getResult()
        ;
    }
}

These Criteria are reusable.

Updating the Homepage

To finish this feature, go back to the homepage. These comment numbers are still including deleted comments. No problem! Open homepage.html.twig, find where we're printing that number, and use article.nonDeletedComments:

... lines 1 - 2
{% block body %}
<div class="container">
<div class="row">
<!-- Article List -->
<div class="col-sm-12 col-md-8">
... lines 10 - 18
<!-- Supporting Articles -->
{% for article in articles %}
<div class="article-container my-1">
<a href="{{ path('article_show', {slug: article.slug}) }}">
... line 24
<div class="article-title d-inline-block pl-3 align-middle">
... line 26
<small>({{ article.nonDeletedComments|length }} comments)</small>
... lines 28 - 30
</div>
</a>
</div>
{% endfor %}
</div>
... lines 36 - 55
</div>
</div>
{% endblock %}

Ok, go back. We have 10, 13 & 7. Refresh! Nice! Now it's 5, 9 and 5.

Next, let's take a quick detour and leverage some Twig techniques to reduce duplication in our templates.

Leave a comment!

  • 2019-04-10 Victor Bocharsky

    Hey Mike,

    That's great you solved it yourself, good job! Unfortunately, I have never done something similar before, so not sure how correct your solution is, but for me it sounds logical, and good discover of ComparisonExpression() method. Can't tell you more, but I think if you think you found a missed use case of CAST() function - fell free to open a PR on beberlei/DoctrineExtensions where explain why you suggest this change and explain your use case. Eventually, your request will be merged or declined but you will get a constructive feedback from maintainers. So, in any case it's double win! ;)

    Though, I can tell you that if you made that change in vendor directory - that's bad :/ You should avoid doing any changes in vendors. Instead, you can create your own extension where copy/paste code from Cast.php and apply your changes, just look how it was created/registered in that package and do the same.

    Also, for such low level operations you can avoid using query builder and use connection where you can write plain SQL. To get a connection instance inside a repository:


    $conn = $this->getEntityManager()->getConnection();
    $conn->query('SELECT * FROM articles');

    For more syntax information take a look at docs: https://www.doctrine-projec...

    Also, I think you can avoid CAST() at all with GROUP BY syntax, but it depends on your query probably, probably you would need to count authors and categories separately. But sometimes 2 simple queries are faster than a big complex one. So, it's good to check it, but don't over-complecate things too much, remember that maintainability of your project is probably better than high performance that you can achieve with complex queries.

    Though, probably better to do more tweak in your DB design, for example, move author to a separate table and refer to authors by ID in articles. Then you would be able to group them easily and with a better performance.

    Cheers!

  • 2019-04-07 Mike

    Update:
    I changed line 37 in the above mentioned custom DQL class for Cast:

    //old
    //$this->fieldIdentifierExpression = $parser->SimpleArithmeticExpression();
    //new
    $this->fieldIdentifierExpression = $parser->ComparisonExpression();

    and how to create the query:

    $this->createQueryBuilder('a')
    ->select('a, (CAST(a.averageRating=:averageRating AS UNSIGNED) + CAST(a.author=:author AS UNSIGNED)) AS matches_count')
    ->setParameter('averageRating', $averageRating)
    ->setParameter('author', $author)
    ->orderBy('matches_count', 'DESC')
    ->getQuery()
    ->getResult();


    Now it works as expected!
    I hope its the right way of doing it and that its the best way for this purpose.
    Maybe it will help someone.

    To improve performance later, I plan to cache 10 ids of recommended articles for every single article page into its own table.
    So it doesn't need to do the calculation on page load.
    This table could get recreated every 24h via a cronjob.

    ID | recommended_article_ids | article_id
    1. |. 10,24,76,88| 5

    Feedback and tips are much appreciated!

  • 2019-04-07 Mike

    I have an advanced question! I want to show related articles on the side. Thanks to your tutorials I came across the Cast() function.
    Thanks to that I can do a query like:

    SELECT
    *,
    (CAST(a.author = :author AS UNSIGNED)
    + CAST(a.category = :category AS UNSIGNED)
    ...
    ) AS matches_count
    FROM article AS a
    ORDER BY matches_count DESC

    It counts the matches and sorts the results by the most related (most matched, based on the "criteria") article.
    The problem is, Doctrine doesn't have the Cast() function.

    After hours of trying and googling, I found out, I have to register my custom DQL Function.
    And thanks god, there is already one, which I registered inside doctrine.yml:
    https://github.com/beberlei...

    I use it inside my ArticleRepository like this:

    $this->createQueryBuilder('a, (CAST(a.category=25 AS UNSIGNED) AS matches_count)')
    ->getQuery()
    ->getResult()
    ;

    But this one doesn't work, I get an error:

    [Syntax Error] line 0, col 29: Error: Expected Doctrine\ORM\Query\Lexer::T_AS, got '='

    The problem seems to be, that the class expects Cast(X as Y) and not Cast (X $comparisonOperator Y).
    And I can't find a solution how to "extend" that Cast() function to fit my needs.

    So my questions:
    1.) Is this solution I came up with the best one to find related articles? Or do you have a better one?
    2.) Could you help me please? Do you know how to extend that Cast class to make Cast(X $comparisonOperator Y) work? Custom DQL Functions seems to be pretty hard for a beginner...

  • 2019-03-11 Victor Bocharsky

    Hey Mike,

    Well, it's better to always use createQueryBuilder(), i.e. use entity repositories. But as in our case, we can't use entity repository *inside* entity, and that's where we can use Criteria. In other words, you can create Criteria in static context, which means you don't need to pass an entity repository object into your models while to use createQueryBuilder() you would need an object of entity repository for example.

    I hope this helps!

    Cheers!

  • 2019-03-10 Mike

    Whats the difference / Why should we use Criteria instead of the CreateQueryBuilder (->andWhere('xyz')) Command?

  • 2019-02-21 Diego Aguiar

    I would recommend you to create a Voter so you can apply all the business rules for determining if a user is able to delete a comment, then just apply the voter for such route action and that's it

    Cheers!

  • 2019-02-21 cybernet2u

    i want to be able to create an Endpoint were a user can delete comments only on the article that he posted, how ?

    so far i got this

    /**
    * @Route("/admin/comment/delete/{c}", name="admin_comment_remove")
    * @Method("DELETE")
    */
    public function removeAdvertComment(EntityManagerInterface $em, $c)
    {

    $comment = $em->getRepository('App:Comment')->findBy(array('id' => $c));
    if (!$comment) {
    echo 'Just Don\'t';
    }
    $em->remove($m);
    $em->flush();


    return new JsonResponse('removed', 204);
    }

  • 2019-02-21 bartek1234321

    Hi, thanks for your reply! passing object in my situation misses the point. You are right to use it as a string and make life much easier :) thanks a lot!

  • 2019-02-20 weaverryan

    Hey bartek1234321!

    I'm not positive, but I believe you need to pass the actual *object* to the second argument. For example, suppose you want to filter where the "type" is equal to some relation object whose id is 5. You *can't* say:


    ->andWhere(Criteria::expr()->eq('type', 5))

    You MUST pas the actual *object* whose id is 5:


    // actually query for this object using the entity manager / repository
    $typeObject = '';
    ->andWhere(Criteria::expr()->eq('type', $typeObject))

    By the way, it looks like this is for a "lookup" table - a table in your database that is mostly full of a few "static" rows that never change. I really don't like these tables - they make your life MUCH more complex than they should :). I would simply avoid the lookup tables and set properties like "type" to a string - e.g. "billing". You can (and should) still use constantly for this, instead of just the strings - but using a string column in the database table instead of a relation to a row that is effectively just hardcoded data anyways, is much nicer. If you have SOME columns in the lookup table that DO change dynamically (e.g. via some admin interface), I would still not make them true relations - I would still use strings and then use that string to query for the XXXType object I need later if I need to use some dynamic value.

    Cheers!

  • 2019-02-20 bartek1234321

    How to use criteria when field is a relation to other entity and i want to filter by this field?
    I've got below error or I do something wrong.
    Cannot match on XXX::type with a non-object value. Matching objects by id is not compatible with matching on an in-memory collection, which compares objects by reference.

    ->andWhere(Criteria::expr()->eq('type', XXXType::BILLING))

  • 2019-02-08 Diego Aguiar

    Hey Cryptoblob

    Are you on PHPStorm? sometimes it happens to me as well. I believe triggering a "Clear index" at Symfony plugin settings may fix it.

    Cheers!

  • 2019-02-07 Cryptoblob

    I have a weird issue, in show.html.twig im getting auto completion, but in homepage.html.twig im not. When i start to type article.non... it doesnt autocomplete for me

  • 2019-01-24 Дмитрий Ченгаев

    Got it. Thanks again.

  • 2019-01-24 Victor Bocharsky

    Hey Dmitriy,

    Yes, exactly, well done! So, if you can't inject something properly, like in this case, just pass it as an argument from somewhere where you have access to it.

    Cheers!

  • 2019-01-24 Дмитрий Ченгаев

    Thank you very much, Victor.

    I understood!

    See my code now. Is that right?



    Twig template:


    ...{{ dump(card.progreses(app.user)) }}...


    App\Entity\Card

    public function getProgreses($user) {
    $criteria = ProgressRepository::createByUserCriteria($user);
    return $this->progreses->matching($criteria);
    }


    
App\Repository\ProgressRepository


    public static function createByUserCriteria($user): Criteria
    {
    return Criteria::create()
    ->andWhere(Criteria::expr()->eq('user', $user))
    ;
    }
  • 2019-01-24 Victor Bocharsky

    Hey Dmitriy,

    No, that's wrong, you should avoid injecting any services into your entities, that's a bad practice. Instead, pass the current user object 2 times.

    So, you need that createByUserCriteria() method as static because you're going to call it from your entities. Then, add an argument to this createByUserCriteria() static method to receive the current user object - you will pass later. So, if you pass it as an argument - you don't need to inject Security service into your repository at all. And the last step, you somehow need to get the current user in getProgreses() method of your entity. So, once again, add a new argument to this method as well requiring the current user.

    In short, you pass the current user from somewhere where you call Card::getProgreses(), and then pass it further to createByUserCriteria() and that's it - simple!

    Cheers!

  • 2019-01-24 Дмитрий Ченгаев

    I am trying to declare this method as not static:


    public function createByUserCriteria(): Criteria
    {
    $user = $this->security->getUser();
    return Criteria::create()
    ->andWhere(Criteria::expr()->eq('user', $user))
    ;
    }

    But in the getter of the corresponding entity I cannot call this repository method. Tell me how to do it right?


    namespace App\Entity;
    ...
    /**
    * @ORM\Entity(repositoryClass="App\Repository\CardRepository")
    * @ORM\Table(name="card")
    */
    class Card {
    ...
    /**
    * @ORM\OneToMany(targetEntity="App\Entity\Progress", mappedBy="card")
    */
    private $progreses;
    ...
    public function getProgreses() {

    $criteria = ProgressRepository::createByUserCriteria();
    return $this->progreses->matching($criteria);
    }
    ...
    }

    How to replace ProgressRepository::createByUserCriteria()?


    namespace App\Entity;
    ...
    /**
    * @ORM\Entity(repositoryClass="App\Repository\CardRepository")
    * @ORM\Table(name="card")
    */
    class Card {
    public function __construct(ProgressRepository $progress_repository)
    {

    $this->progreses = new ArrayCollection();
    $this->progress_repository = $progress_repository;
    }

    public function getProgreses() {

    $criteria = $this->progress_repository->createByUserCriteria();
    //$criteria = ProgressRepository::createByUserCriteria();
    return $this->progreses->matching($criteria);
    }
    }

    This option returns error: "Call to a member function createByUserCriteria() on null"

  • 2019-01-23 Diego Aguiar

    Hey Дмитрий Ченгаев

    Oh, yes, you can't use any instance fields in static methods, so you have to declare that method as non static or find a way to pass in the User

    Cheers!

  • 2019-01-23 Дмитрий Ченгаев

    Victor, Thanks for the answer.

    Here is my code:

    use Symfony\Component\Security\Core\Security;
    ...
    class ProgressRepository extends ServiceEntityRepository {
    private $security;

    public function __construct( Security $security, RegistryInterface $registry ) {
    parent::__construct($registry, Progress::class);

    $this->security = $security;
    }

    public static function createByUserCriteria(): Criteria
    {
    $user = $this->security->getUser();
    return Criteria::create()
    ->andWhere(Criteria::expr()->eq('user', $user))
    ;
    }
    }

    But i have error: "$this is not accessible in static context". How can I fix this?

  • 2019-01-23 Victor Bocharsky

    Hey Dmitriy,

    The easiest is to pass the current user object as an argument to the create() method. But it depends on where you call it. If you call that method from a controller - it's easy. But if you call it from the entity - you need another way. So, in this case you can inject "Symfony\Component\Security\Core\Security" service into your repository and then you will have a convenient getUser() method to fetch the current user.

    Cheers!

  • 2019-01-23 Дмитрий Ченгаев

    I need a Criteria by User object:

    public static function createByUserCriteria (): Criteria
    {

    return Criteria :: create ()
    -> andWhere (Criteria :: expr () -> eq ('user', $user))
    ;
    }

    How can I get the current user in EntityRepository?

  • 2018-08-20 Qcho

    Thanks!

  • 2018-08-20 Victor Bocharsky

    Hey Qcho,

    Unfortunately, if you start using ArrayCollection::matching($criteria) function to match the passed criteria - you need to explicitly specify things, including orderBy(). The OrderBy annotation is applied when you just return $this->comments.

    Cheers!

  • 2018-08-20 Qcho

    Question.
    Because the createdAt property already has the `@ORM\OrderBy` annotation. Do we need to add it again in the Criteria example? Isn't just inherited from the base query done?

  • 2018-08-16 ssdk86

    Thanks, Diego!
    I understood, it`s like class MarkdownHelper and class SlackClient in folder Service in your symfony 4 course.

  • 2018-08-15 Diego Aguiar

    Hey ssdk86

    A controller is the middleware of the request of a user and the response. So, you may code a little bit of logic into a controller's method, but all the business decisions must be handled by a service class (it's just another layer for delegating the actual work)

    Cheers!

  • 2018-08-15 ssdk86

    Hi, you say:
    "One thing I don't like about the Criteria system is that I do not like having query logic inside my entity. And this is important! To keep my app sane, I want to have 100% of my query logic inside my repository. No worries: we can move it there!"

    And I have a question as to the best practices of constructing the MVC pattern in Symphony 4
    Let's say I have a VideoController, which indicates the route, tvig tmp, etc.., but I also want to use the convertvideo method. And that's where I have to keep this method right?
    Perhaps I must created folder model, which placed class VideoController and make it all the additional methods?

  • 2018-08-07 Diego Aguiar

    Hey Doccos

    The problem with your approach is that the "roles" field in User is a JSON string, so you can't use the "IN" statement like that, probably you could do a "fuzzy search" (e.g. field LIKE %some_text%), but you have to be extra careful because you are working with roles, and roles means security access, or probably you may want to give a look to this library https://github.com/Syslogic...

    Cheers!

  • 2018-08-06 Doccos

    I have a little problem, i tried to add a Criteria ->andWhere(Criteria::expr()->in('roles', ['ROLE_USER']));
    The row Roles has the type JSON but everytime i get null result

  • 2018-07-16 Victor Bocharsky

    Hey toporovvv ,

    I think it's a good idea, and yeah, traits may help here a lot. Well, it will increase complexity of your code, but yeah, you'll reduce duplications. But as always, try do not overthink it too much, you need to find a good balance. It makes sense to do so for some complex queries that are duplicated in your project, but probably sometimes it's better to do some minor duplications instead. So it depends on your use cases, just try to find a good balance.

    Cheers!

  • 2018-07-12 toporovvv

    I've personally found this part is super-exciting - https://knpuniversity.com/s... We can create a helper class with all this static methods with different Criterias (maybe add all them in CriteriaTrate as regular methods?). And than use as some sort of "query mixins". This method could reduce a code duplication.