Criteria System: Champion Collection Filtering

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!

I made a typo. Already changed the original post.

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!

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

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!

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.

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!

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!

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!

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.

Awesome! And great question :)

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!

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!

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!

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!

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']);

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

Regards,

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!