Buy

I setup my fixtures so that about half of my FortuneCookies have a "discontinued" value of true. For our startup fortune cookie company, that means we don't make them anymore. But we're not showing this information anywhere on the frontend yet.

But what if we wanted to only show fortune cookies on the site that we're still making? In other words, where discontinued is false. Yes yes, I know. This is easy. We could just go into CategoryRepository and add some andWhere() calls in here for fc.discontinued = true.

But what if we wanted this WHERE clause to be added automatically, and everywhere across the site? That's possible, and it's called a Doctrine Filter.

Creating the Filter Class

Let's start by creating the filter class itself. Create a new directory called Doctrine. There's no real reason for that, just keeping organized. In there, create a new class called DiscontinuedFilter, and make sure we put it in the right namespace:

8 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
<?php
namespace AppBundle\Doctrine;
class DiscontinuedFilter
{
}

That's a nice blank class. To find out what goes inside, Google for "Doctrine Filters" to get into their documentation. These filter classes are simple: just extend the SQLFilter class, and that'll force us to have one method. So let's do that - extends SQLFilter. My IDE is angry because SQLFilter has an abstract method we need to add. I'll use PHPStorm's Code->Generate shortcut and choose "Implement Methods". It does the work of adding that addFilterConstraint method for me. And for some reason, it's extra generous and gives me an extra ClassMetadata use statement, so I'll take that out.

23 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
... lines 1 - 2
namespace AppBundle\Doctrine;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;
class DiscontinuedFilter extends SQLFilter
{
/**
* Gets the SQL query part to add to a query.
*
* @param ClassMetaData $targetEntity
* @param string $targetTableAlias
*
* @return string The constraint SQL if there is available, empty string otherwise.
*/
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
// ...
}
}

Ok, here's how this works. If this filter is enabled - and we'll talk about that - the addFilterConstraint() method will be called on every query. And this is our chance to add a WHERE clause to it. The $targetEntity argument is information about which entity we're querying for. Let's dump that to test that the method is called, and to see what that looks like:

23 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
... lines 1 - 7
class DiscontinuedFilter extends SQLFilter
{
... lines 10 - 17
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
var_dump($targetEntity);die;
}
}

Adding the Filter

Next, Doctrine somehow has to know about this class. If you're using Doctrine outside of Symfony, you'll use its Configuration object and call addFilter on it:

// from http://doctrine-orm.readthedocs.org/en/latest/reference/filters.html#configuration
$config->addFilter('locale', '\Doctrine\Tests\ORM\Functional\MyLocaleFilter');

You pass it the class name and some "key" - locale in their example. This becomes its nickname, and we'll refer to the filter later by this key.

In Symfony, we need the same, but it's done with configuration. Open up app/config/config.yml and find the doctrine spot, and under orm, add filters:. On the next line, go out four spaces, make up a key for the filter - I'll say fortune_cookie_discontinued and set that to the class name: AppBundle\Doctrine\DiscontinuedFilter:

76 lines app/config/config.yml
... lines 1 - 46
doctrine:
... lines 48 - 62
orm:
... lines 64 - 65
filters:
fortune_cookie_discontinued: AppBundle\Doctrine\DiscontinuedFilter
... lines 68 - 76

Awesome - now Doctrine knows about our filter.

Enabling a Filter

But if you refresh the homepage, nothing! We do not hit our die statement. Ok, so adding a filter to Doctrine is 2 steps. First, you say "Hey Doctrine, this filter exists!" We just did that. Second, you need to enable the filter. That ends up being nice, because it means you can enable or disable a filter on different parts of your site.

Open up FortuneController. Let's enable the filter on our homepage. Yes yes, we are going to enable this filter globally for the site later. Just stay tuned.

To enable it here, first, get the EntityManager. And I'm going to add a comment, which will help with auto-completion on the next steps:

69 lines src/AppBundle/Controller/FortuneController.php
... lines 1 - 10
class FortuneController extends Controller
{
... lines 13 - 15
public function homepageAction(Request $request)
{
/** @var EntityManager $em */
$em = $this->getDoctrine()->getManager();
... lines 20 - 36
}
... lines 38 - 67
}

Once you have the entity manager, call getFilters() on it, then enable(). The argument to enable() needs to be whatever nickname you gave the filter before. Actually, I have a typo in mine - I'll fix that now. Copy the fortune_cookie_discontinued string and pass it to enable():

69 lines src/AppBundle/Controller/FortuneController.php
... lines 1 - 15
public function homepageAction(Request $request)
{
/** @var EntityManager $em */
$em = $this->getDoctrine()->getManager();
$em->getFilters()
->enable('fortune_cookie_discontinued');
... lines 22 - 36
}
... lines 38 - 69

Filter class, check! Filter register, check! Filter enabled, check. Moment of truth. Refresh! And there's our dumped ClassMetadata.

Adding the Filter Logic

We haven't put anything in DiscontinuedFilter yet, but most of the work is done. That ClassMetadata argument is your best friend: this is the Doctrine object that knows everything about the entity we're querying for. You can read your annotation mapping config, get details on associations, find out about the primary key and anything else your heart desires.

Now, this method will be called for every query. But we only want to add our filtering logic if the query is for a FortuneCookie. To do that, add: if, $targetEntity->getReflectionClass() - that's the PHP ReflectionClass object, ->name() != AppBundle\Entity\FortuneCookie, then we're going to return an empty string:

27 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
... lines 1 - 7
class DiscontinuedFilter extends SQLFilter
{
... lines 10 - 17
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
if ($targetEntity->getReflectionClass()->name != 'AppBundle\Entity\FortuneCookie') {
return '';
}
... lines 23 - 24
}
}

It's gotta be an empty string. That tells Doctrine: hey, I don't want to add any WHERE clauses here - so just leave it alone. If you return null, it adds the WHERE but doesn't put anything in it.

Below this, it's our time to shine. We're going to return what you want in the WHERE clause. So we'll use sprintf, then %s. This will be the table alias - I'll show you in a second. Then, .discontinued = false. This is the string part of what we normally put in an andWhere() with the query builder. To fill in the %s, pass in $targetTableAlias:

27 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
... lines 1 - 17
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
if ($targetEntity->getReflectionClass()->name != 'AppBundle\Entity\FortuneCookie') {
return '';
}
return sprintf('%s.discontinued = false', $targetTableAlias);
}
... lines 26 - 27

Remember how every entity in a query has an alias? We usually call createQueryBuilder() and pass it something like fc. That's the alias. In this case, Doctrine is telling us what the alias is so we can use it.

Alright. Refresh! Um ok, no errors. But it's also not obvious if this is working. So look at the number of fortune cookies in each category: 1, 2, 3, 3, 3, 4. Go back to FortuneController and delete the enable() call. Refresh again. Ah hah! All the numbers went up a little. Our filter is working.

Put the enable() call back and refresh again. Click the database icon on the web debug toolbar. You can see in the query that when we LEFT JOIN to fortune_cookie, it added this f1_.discontinued = false.

Woh woh woh. This is more amazing than I've been promising. Even though our query is for Category's, it was smart enough to apply the filter when it joined over to FortuneCookie. Because of this, when we call Category::getFortuneCookies(), that's only going to have the ones that are not discontinued. The filter is applied if the fortune cookie shows up anywhere in our query.

Passing Values to/Configuring a Filter

Sometimes, like in an admin area, we might want to show only discontinued fortune cookies. So can we control the value we're passing in the filter? To do this, remove false and add another %s. Add another argument to sprintf: $this->getParameter('discontinued'):

27 lines src/AppBundle/Doctrine/DiscontinuedFilter.php
... lines 1 - 17
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
... lines 20 - 23
return sprintf('%s.discontinued = %s', $targetTableAlias, $this->getParameter('discontinued'));
}
... lines 26 - 27

This is kind of like the parameters we use in the query builder, except instead of using :discontinued, we concatenate it into the string. But wait! Won't this make SQL injection attacks possible! I hope you were yelling that :). But with filters, it's ok because getParameter() automatically adds the escaping. So, it's no worry.

If we just did this and refreshed, we've got a great error!

Parameter 'discontinued' does not exist.

This new approach means that when we enable the filter, we need to pass this value to it. In FortuneController, the enable() method actually returns an instance of our DiscontinuedFilter. And now we can call setParameter(), with the parameter name as the first argument and the value we want to set it to as the second:

70 lines src/AppBundle/Controller/FortuneController.php
... lines 1 - 15
public function homepageAction(Request $request)
{
/** @var EntityManager $em */
$em = $this->getDoctrine()->getManager();
$filters = $em->getFilters()
->enable('fortune_cookie_discontinued');
$filters->setParameter('discontinued', false);
... lines 23 - 68
}

Refresh! We see the slightly-lower cookie numbers. Change that to true and we should see really low numbers. We do!

Enabling a Filter Globally

Through all of this, you might be asking: "What good is a filter if I need to enable it all the time." Well first, the nice thing about filters is that you do have this ability to enable or disable them if you need to.

To enable a filter globally, you just need to follow these same steps in the bootstrap of your app. To hook into the beginning process of Symfony, we'll need an event listener.

I did the hard-work already and created a class called BeforeRequestListener:

19 lines src/AppBundle/EventListener/BeforeRequestListener.php
... lines 1 - 7
class BeforeRequestListener
{
public function __construct(EntityManager $em)
{
$this->em = $em;
}
public function onKernelRequest(GetResponseEvent $event)
{
// ...
}
}

For Symfony peeps, you'll recognize the code in my services.yml:

10 lines app/config/services.yml
services:
before_request_listener:
class: AppBundle\EventListener\BeforeRequestListener
arguments: ["@doctrine.orm.entity_manager"]
tags:
-
name: kernel.event_listener
event: kernel.request
method: onKernelRequest

It registers this as a service and the tags at the bottom says, "Hey, when Symfony boots, like right at the very beginning, call the onKernelRequest method." I'm also passing the EntityManager as the first argument to the __construct() function. Because, ya know, we need that to enable filters.

Let's go steal the enabling code from FortuneController, take it all out and paste it into onKernelRequest. Instead of simply $em, we have $this->em, since it's set on a property:

23 lines src/AppBundle/EventListener/BeforeRequestListener.php
... lines 1 - 14
public function onKernelRequest(GetResponseEvent $event)
{
$filter = $this->em
->getFilters()
->enable('fortune_cookie_discontinued');
$filter->setParameter('discontinued', false);
}
... lines 22 - 23

Let's try it! Even though we took the enable() code out of the controller, the numbers don't change: our filter is still working. If we click into "Proverbs", we see only 1. But if I disable the filter, we see all 3.

That's it! You're dangerous. If you've ever built a multi-tenant site where almost every query has a filter, life just got easy.

Leave a comment!

  • 2016-10-16 avknor

    Thanks for your help weaverryan !

  • 2016-10-15 weaverryan

    Hey avknor!

    Ok, let's see if we can work this out :).

    First, some background - which I'm pretty sure you understand - but in case it's useful for others in the future :)

    When you use a filter, what you're actually doing is writing raw SQL that will be applied to your query - i.e. you are *not* writing the same type of code that you might put into a query builder (where we're building DQL, which is a little friendlier). For example, in DQL, you can set a parameter to an array for a WHERE IN statement, and when Doctrine translates this to the SQL string, it properly takes that array and implodes it as expected. But, that won't happen in a filter: ultimately what we need to return from addFilterConstraint() is a raw SQL string.

    In fact, if you dig into the core of Doctrine, here's what the (summarized) code looks like that calls your filter:


    $filterClauses = array();
    foreach ($filters as $filter) {
    if ($filterExpr = $filter->addFilterConstraint($targetEntity, $targetTableAlias)) {
    $filterClauses[] = '(' . $filterExpr . ')';
    }
    }

    // this is ultimately added to the raw SQL string, after the WHERE
    return implode(' AND ', $filterClauses);

    Now, you tried to setParameter() and pass it an array... which makes perfect sense. The reason this doesn't work is simply because Doctrine apparently doesn't support this - you're not doing anything wrong. Here's the related issue: https://github.com/doctrine/do... and pull request to add the feature that was never merged (https://github.com/doctrine/do.... So, hopefully that at least makes you feel better - you *will* need some sort of a hack to make this happen.

    About the relational field comment, in that case, since we're ultimately building raw SQL, you'd just set the id of the related entity via setParameter() (not the entire object) or an array of ids, via the hack - or "crutches" as you call it - I like that term... but don't like that it's necessary :).

    Let me know if this helps! This was a shortcoming of Doctrine filters that I honestly wasn't aware of - the use-case makes perfect sense, but I had never hit it myself.

    Oh, and by the way, the Gedmo DoctrineExtensions library has an interesting SoftDeletableFilter for 2 reasons:

    1) They don't use getParameter(), and instead use some low-level methods to do some quoting. They don't use it here, but they could also manually quote a value by using $conn->quote().

    2) They have a `$disabled` array property (which is not meant to be a parameter, but is still configuration) and they allow you to set this via a few public methods. What I mean is, in your case, you could actually avoid setParameter() and instead call your own public method on your filter to set the array of ids. This is still a little bit of a "crutch" but less than needing to json_encode and then remove quotes afterwards.

    Cheers!

  • 2016-10-15 avknor

    This is not actualy what I wanted. Let say: what if I wanted to filter by few values of field? For records of 2015 and 2016 years for example... So i need to set some sort of array-of-years in ->setParameter. But it want only strings! And sends an error when i'm trying to set something else.
    How do you solve this?

    Or even more complicated example. What if I need to filter by relational field. In this case I need to set entity as param of filter. Or even ArrayCollection of entities!

    For now I'm decide it like this: I json_encode array before set it to setParameter in controller. And then I json_encode it in Filter class. BUT! There in Filter class I need to make one more step. I need to remove single and doublequotes from json string. Because they was added by setParameter to escape string (thats why we love it )) ).

    Code hacks like this we call "crutches" here in Russia. So I'd like to avoid of them and write more elegant code )

  • 2016-10-15 weaverryan

    Hey!

    I'm not sure off the top of my head - but it looks like you got a reply on SO that makes sense to me. Let us know if it works out!

    Cheers!

  • 2016-10-14 avknor

    Filters are grate! But I can't find any info about how to set one filter to one query many times with different parameters ((
    Please help me if you can. Deadline is killing me ))

    I made Q at Stackoverflow http://stackoverflow.com/quest...

  • 2016-08-20 Monika Szandała

    Thank you very much for your help.

    And my addFilterConstraint function may look like that:

    $parameters = array();
    if($this->hasParameter('discontinued'))
    {
    $parameters[] = sprintf('%s.discontinued LIKE %s', $targetTableAlias, $this->getParameter('discontinued'));
    }

    if($this->hasParameter('discontinuedDate'))
    {
    $parameters[] = sprintf('%s.discontinuedDate = %s', $targetTableAlias, $this->getParameter('discontinuedDate'));
    }

    $result = implode("AND ", $parameters);

  • 2016-08-19 weaverryan

    Hey Monika!

    Hmm, let's see. So, in this chapter, we have just one parameter: discontinued. Suppose we need *another* parameter - e.g. "discontinuedDate" (just for an example). You *should* be able to do this, simply by setting *both* parameters:


    $filter = $this->em
    ->getFilters()
    ->enable('fortune_cookie_discontinued');
    $filter->setParameter('discontinued', false);
    $filter->setParameter('discontinuedDate', new \DateTime('-1 day'));

    Does that help answer your question? Let me know either way :).

    Cheers!

  • 2016-08-19 Monika Szandała

    How can I use more then one parameters in Filter. Do I need to add another filter in config.yml with the same class name of filter?

  • 2016-06-22 Miryafa

    For anyone who just looked at this page without doing the rest of the course up to this point (as with me) and/or aren't using Symfony: You can access the current configuration with the following two lines:

    $em = $this->getServiceLocator()->get('Doctrine\ORM\EntityManager');
    $config = $em->getConfiguration();

    And you just put those lines in the indexAction of your controller (or whichever action you want). So the $config line would look like this:

    public function indexAction()
    {

    // ... other code ...

    // from http://doctrine-orm.readthedoc...
    $em = $this->getServiceLocator()->get('Doctrine\ORM\EntityManager');
    $config = $em->getConfiguration();
    $config->addFilter('locale', '\Doctrine\Tests\ORM\Functional\MyLocaleFilter');

    // ... other code ...

    }

  • 2016-05-25 Tomáš Votruba

    Recently I wrote and article about decoupled Filters and how to build them:
    http://www.tomasvotruba.cz/blo...

    It's easy if you try :)

  • 2016-03-18 Tomáš Votruba

    Thanks!

  • 2016-03-18 weaverryan

    I like it!

  • 2016-03-18 Tomáš Votruba

    If you prefer using only Filter class and keep Controllers clean, you might like this package https://github.com/Symplify/Mo...

  • 2016-03-18 Tomáš Votruba

    Hi, this might be a solution for you: https://github.com/Symplify/Mo...

    It doesn't depend on Controller, nor Doctrine yaml configuration, nor Doctrine bundle. It is standalone and easily portable to any project.

  • 2015-08-05 WarGot Georg

    Thanks for answer.
    Big big sorry. I write answer for you with my code, but I later understand what I use query for database, this query also call sql filter, before I send parameter to this filter. Dead loop.

    Code after
    http://codepen.io/anon/pen/OVd...
    before
    http://codepen.io/anon/pen/xGM... attention to the line 32

    Thanks, good luck. Perfect article.

    And as always, sorry my english -)

  • 2015-08-05 weaverryan

    Hey there!

    I think your English is fine :). I don't know what your problem is, but the finished code for this tutorial *does* work (I just re-tried it). Specifically, in this sections - https://knpuniversity.com/scre... - if you don't call setParameter() in the listener, then you'll get the error "Parameter 'discontinued' does not exist." If you do not get this error, then I think it *is* working.

    But one warning: after you call setParameter(), Doctrine "escapes" the value before passing it to your filter. So, if you pass it the string hello, the parameter will become 'hello' (a string with quotes in the string). If you pass false (like I do), this will become 0. So, it's possible that you're passing some value and it's being escaped in some unexpected way.

    Good luck!

  • 2015-08-05 WarGot Georg

    Good afternoon.
    There is a problem. In the filter, I do not get the parameter from eventlistener. The problem is then I don`t use controller. What to do.
    Doctrine filter work before event listener.
    Thanks.
    P.S. Sorry my english -)

  • 2015-07-21 weaverryan

    Criteria are a bit more interesting than I thought - I will admit I like the fluid interface that can be used to either make a query (with matching) or filter a collection (though I want to minimize doing this sort of thing).

    If I start finding uses for it in my code, then I may add a chapter on it. It looks like a nice feature - but I'm not convinced yet that I'll have a pattern where I use it. Your point about using them in controllers is valid, but I still think I'll create custom repo methods. There's a lot of personal preference down at this level.

    Cheers!

  • 2015-07-20 Victor Bocharsky

    Wow.. :) I agree with you, Criteria more complex at first look and the beginners hard to use it. However, I could use criteria without custom entity repositories for minor things in any controller calling `matching()` method on entity repository. It's allowing me to avoid mixing layers. I mean using query builder in controllers is a wrong way because it mixing DB layer with controller business logic layer that violates MVC pattern, so this is a bad approach. Creating entity repository with many custom methods in some cases could be overhead. So in this cases criteria could help as well.

    Also I like to easily apply criteria to already fetched Doctrine array collections calling `matching()` method on it. It's allowing me filtering any collection and avoid sending a new query to database again (filtering on PHP collection level).

    Summing up, I think about *doctrine criteria* like about *array criteria* on steroids that I could passed to `fetchBy` and `fetchOneBy` methods :) It allowing me to use negotiation (NOT), partial matching (LIKE), checking for NULL and OR logical operations, >, < operators etc. that I can get directly in controller and that not supported by `fetchBy` and `fetchOneBy` methods out-of-the-box.

  • 2015-07-20 weaverryan

    Victor Bocharsky maybe...? :) I've never used them and avoid them on purpose because they always strike me as a really complicated way of doing something that could be done very easily inside a DQL string. So, instead of all the expression/criteria stuff (example http://stackoverflow.com/quest..., I'll just put what I need inside of an andWhere() string, like we do here: https://knpuniversity.com/scre....

    That's subjective - but it looks much easier to me than the Criteria stuff. But there may also be a use-case that can only be done with Critiera?

    Cheers!

  • 2015-07-20 Victor Bocharsky

    It will be great to add a new screencast about Doctrine Criteria to this tutorial. I think they awesome too! :)
    What do you think, Ryan?

  • 2015-03-10 weaverryan

    I think you've summarized the options (that I know of) nicely:

    1. I agree with your con
    2. I think you're over-estimating the performance loss. And if you hydrate "extra lazy" and do a count of the children, the extra query will be done automatically.
    3. Agree its over-kill
    4. Yea, this may be slower than option (2)

    So, I would do 2 - I'm certain it's not that big of a deal. And if it were, I'd start to employ other strategies - e.g. caching. The only exception is if I were processing MANY rows (e.g. making a CSV download) where I needed to keep memory managed. In that case, I'd probably be doing a low-level query (e.g. something like option 1, but with a Doctrine "iterator").

    Cheers!

  • 2015-03-10 Pietrino Atzeni

    :))) Maybe I'm trying to do something strange, but what I'd like to accomplish is something like this: I want to view a list of all "parent" classes, with a "count" of all their children (such as "users" and their "posts"). Now, I think the choices I have are:

    1. hydrate arrays (just as you made in chapter "Selecting specific fields";
    2. perform multiple queries (1 + 1*"number of parents") to retrieve all the children of each parent;
    3. create a custom hydrator;
    4. use joins.

    Cons:
    1. I will miss the objects logic;
    2. I will kill performances because of queries;
    3. seems overkilling, looking at the Doctrine docs;
    4. I will kill performances if children hydrating tons of objects

    Any idea?

  • 2015-03-10 weaverryan

    Hi Pietrino!

    To be honest, I've never tried this before! But as far as I can tell, this is not possible. The only possibility I can think of is with a custom hydrator. That looks possible, but might not be worth it. I think part of the issue would be that if you were able to put this in annotations (e.g. something attached to the Category entity), Doctrine might not be able to perform the query needed to do that count. For example, counting "totalCookies" for a Category would require a join. So Doctrine would need you to add that join, it would need to make it automatically, or it would need to do a separate query. And the last option is already sort of possible - e.g. count($category->getCookies()) - which will be done by doing a fast COUNT() query if you use the extra lazy association: http://doctrine-orm.readthedoc...

    It's possible I'm over-looking something in Doctrine, but this is what I know :).

    Cheers!

  • 2015-03-10 Pietrino Atzeni

    Hi Ryan, great video as usual! Many things learned on the journey. I have still two questions that relate to filtering:

    1. can we fetch objects (not arrays) with "extra" columns using DQL? For example, an object "Category" with the property "totalCookies" automatically hydrated?
    2. if we can, can we also "declare" this "automatic hydration" in annotations (or yml/xml)?

    Thanks,
    Pietro