Collection Magic with Criteria

Very good advice imo not overthink and not prematurely optimize. When you want to look good programmers, you might start thinking long before making best solution, but you end up not being good in relation to the cost and missing deadlines if you overthink and prematurely optimize. Plus prematurely optimizing can make code more complex, which again takes more time to understnad, and easier to create bugs in it.

Btw I am surprised I learned so much new things in this course. I had been working with doctrine for a while and watched some videos already, and I thought probably I will not learn a lot new from this. Of course I could live with what I know, otherwise I would have learned earlier, but maybe will find where to use those new things, at least some of them.

Thanks for the *super* nice comments ❤️ (and as you know, I agree 100!). And glad you learned something too - I'm the same way - if I'm at a conference, I almost always get good info out - so much stuff to know :).

Cheers!

In ArticleRepository, create a public static function called createNonDeletedCriteria() that will return a Criteria object: and next...
In the paragraph we write the article repository, and write in the comment code. Fix it

Hey Ozornick,

Thank you for this report! We would be glad to fix the problem, but could you explain a bit clearer what exactly you want to be fixed? Unfortunately, I didn't get what you mean :/ Maybe any examples, like actual code and expected code, etc? It would be really appreciated.

Cheers!

https://ibb.co/cQ034Zv
https://ibb.co/KN38Jn3
Sorry for my design. I think it’s worth fixing the ArticleRepository on the CommentRepository

Hey Ozornick,

Ah, I see now! Thank you again for the more detailed feedback. I just fixed it

Cheers!

1. This is kinda complicated. I want to make sure that it's not outdated in Symfony 5? Is there a better method now?
2. Why don't you just make a query in the Repository with andWhere(is_deleted = 0); To get the non deleted comments?

Hey Farry,

1) Basically this topic is related to Doctrine and not to Symfony, so you don't have to change anything if you're using Symfony5 for this project

2) Yes, you can do that but it depends on how you want to work with your objects, if you really want to call $article->getNonDeletedComments() then you need a criteria on that method, but anyways, this is done in purpose to exhibit what can be done with Doctrine's Criteria system

Cheers!

Hi Ryan, I used this cool trick, but now it shoots me in the foot. It appears (to me) that the criteria trick is not able to be serialized when using messenger. It fails my twig email templates to be rendered correctly when using criteria in a collection that is used in the twig template when using a async transport. It took me a while to discover that the error is in using criteria on the collection.

Are you aware of this and if so, do you know how to conquer this?

It turned out that in this case I had the set fetch="EAGER" on the relation that has the criteria used in the collection

Hey Paul Rijke!

Interesting! Are you storing the entire entity object itself on your message? If so, I'd recommend (even though it's a bit more work) only storing the id of the entity object in your message. Then, in your handler, you query for a fresh version of that entity from the database. That'll avoid any serialization problems. And, maybe more importantly, it'll make sure your entity in your message handle isn't out of date with the database :).

Cheers!

I am not creating the message, mailer does and it stores the whole object I think:-) It is a async mail message. I will rethink how to do this with id's instead of objects then... I have no idea (yet)

Hey Paul Rijke!

Ah! You're passing an entire entity object into the "context" of the mail? That makes sense. Unfortunately, what you'll probably need to do is pass in the individual pieces of information as context instead of the entire object. That's annoying, but it should fix the issue :).

Cheers!

Hi, I'm learning Symfony and Doctrine thanks to Sfcasts, so thank you very much for your work. In this chapter i didn't understand why we put the querybuilder->addCriteria inside findAllPublishedOrderedByNewest() if we want to re-use the logic from Criteria. thanks for yout time

Hey Gaetano S.

We move the criteria logic into a public static function inside ArticleRepository so it can be re-usable by the Article entity and by the findAllPublishedOrderedByNewest() method. Does it makes more sense now?

Cheers!

ok, I thought it was possible to create a method like addIsPublishedQueryBuilder() and then call it inside createNonDeletedCriteria(). I was a little bit confused with this Criteria concept. I have to meditate on it :). Thanks for your help

You can arrange your code however it's more convenient for you but you just need to be aware that you cannot reference to $this variable inside a static method

For those who struggling filtering collection of entities with @Embedded ones. AFAIK it's not possible to apply Criteria API to the property of @Embedded entity. You can be saved with \Doctrine\Common\Collections\ArrayCollection::filter method

Hey Egor,

Thank you for this tip! I've never done it before though, does not know much about it. Criteria might be tricky and optimized sometime, but yeah, filter() should be a good workaround instead. And in case you use it - it also might be a good idea to keep an eye on performance, big collections might consume a lot of memory.

Cheers!

I am trying to use criteria with a Category entity which use translatable of Doctrine2 behaviors:
In my category entity:
`/**

	 * @return Collection|self[]
	 */
	public function getNonDeletedChildren(): Collection
	{
		$criteria = CategoryRepository::createNonDeletedCriteria();
		return $this->children->matching($criteria);
	}`

In my Category repository:

` public static function createNonDeletedCriteria() : Criteria

	{
		return Criteria::create()
		               ->andWhere(Criteria::expr()->eq('isDeleted', false))
		               ->orderBy(['position', 'ASC']);
	}`

In my twig template:

`{% if (category.children|length > 0) %}

                        {% for child_category in category.nonDeletedChildren %}
                            {{ include('admin/admin_category/_category_children.html.twig', {
                                category: child_category,
                                level: 1
                            }) }}
                        {% endfor %}
                    {% endif %}`

I am getting the error:
<blockquote>An exception has been thrown during the rendering of a template ("Warning: call_user_func_array() expects parameter 1 to be a valid callback, class 'App\Entity\CategoryTranslation' does not have a method 'get1'").</blockquote>

The error seems to be related to : {% for child_category in category.nonDeletedChildren %}

Even if I just do
category.nonDeletedChildren.empty
I got the error:
<blockquote>An exception has been thrown during the rendering of a template ("Unrecognized field: 0").</blockquote>

Any idea?
Thx!

oups I have found the issue. My code is wrong:
<blockquote>->orderBy(['position', 'ASC']);</blockquote>
instead of
<blockquote>->orderBy(['position' => 'ASC'])</blockquote>

Sorry.

Hey be_tnt
I'm happy to hear that you found your problem, and no worries we all make mistakes!

Cheers!

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...

<b>Update:</b>
I changed line 37 in the above mentioned custom DQL class for Cast:
<blockquote> //old

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

</blockquote>and how to create the query:
<blockquote>$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();

</blockquote>
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!

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-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#data-retrieval-and-manipulation

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!

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

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!

Hey Victor,

1) But to use CommentRepository inside ArticleRepository, is valid?

2) Also, why we can't use CommentRepository inside Article (entity) - cause I see it already uses ArticleRepository?

3) According to this tutorial, we can re-use the Criteria logic in Query Builder, inside the ArticleRepository class.
Is this even suppose to work?
I mean, if the QueryBuilder queries only from the table the Repository is in, how can we re-use a logic having a field ("isDeleted" in this case) that doesn't exist in the Article entity?
Basically we mix 2 entities (tables) in the same query without joining any tables.

Hey Adrian,

1. Hm, do we use CommentRepository inside ArticleRepository? I don't see it, at least in this chapter. What do you mean by this? Using like calling CommentRepository's static methods in ArticleRepository? Or injecting CommentRepository into ArticleRepository?

2. Why you can't? You can :) Actually, you can do whatever you want, the question is.. is it a good idea? But in this case it's totally OK. You're kinda confusing static calls. We just want to reuse those Doctrine Criteriain a few places... for this we created a static method to be able to call it without actually injecting real services, because injecting repositories into entities are bad idea. But with static calls we do not "inject" them, but just reuse some part of code. You could put those Doctrine Criteria in an Article entity class, but since it's something related to the query it makes sense to put it into repository. What repository - not a big deal. You could put it into CommentRepo instead of ArticleRepo and use that one instead. So once again, it's not injecting repositories into entities, we just reuse some static code, and you can put that into any class that makes more sense to you.

3. Does it work in the screencast? :) Of course, if your entity does not have isDeleted property - it won't work and you came into fatal error. But it's up to you what to call in your code. Well, I still don't think we mix 2 entities (tables) here, because we're talking about static context and Doctrine Criteria. We could write those criteria in Article entity and it would be totally fine as well, but since doctrine criteria is kinda part of the query and all your custom queries should live in repositories - we decided to move it into the repo. SO, do you have a better idea where to put that static Doctrine Criteria instead of the repo? It might be a better place, I'd not argue on it, bit it's fine to have it in that repo as well :)

Cheers!

Thanks for clarifying, I did kinda confused static functions for this purpose.
I don't know why :)... maybe because Symfony is a little confusing until you completely go pro with it :)).
Hardest Framework out there and most SOLID one I know... so harder to decipher how all parts are connected with all the extends, implements, etc.
I don't mean this in a bad way - it's just an observation.

Personally, I will avoid using Criteria in cases like this... it's more clear if I just use the Query Builder.
At least this is how I see it now, maybe my opinion will change in specific cases, who knows.

P.S.: But it's nice that these tutorials really define complex scenarios, even if the subject is somewhat dull, like articles, comments, etc.
Somehow the author makes simple things by using complex methods :)). Unnecessary for this case, but completely uncovers more functionality from Symfony. Really nice (and for sure very informative even for people that already use Symfony since it's inception)!

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);
}`

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!

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))

Hey bartek!

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!

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!

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

Hey Tom

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!

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?

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!

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?

Hey Dmitriy

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!

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"

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!

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))
		;
}

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!

Got it. Thanks again.

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?

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!

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?

Hey Aleksandr T.

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!