Simple Filtering in Twig

In this video we will add in a very simple filter input to our Twig CRUD input, allowing the end user to add in some text which must be contained in the blog post's title field to be shown in the result set.

Before proceeding further with this it is best to be aware that this is a very simplistic approach. The query we will use is a LIKE query combined with %'s at the beginning and the end of the string. This will cause a full table scan which is particularly poor for performance, and doesn't scale well at all.

A better alternative - if you must use MySQL in this way - is to use FULLTEXT search. I haven't tried this with Doctrine myself, but this article may be a good starting place. This is a technique I used to use heavily prior to using frameworks, and I can vouch for how well it works.

Doctrine does support FULLTEXT indexes, but I cannot find any documentation on how this should be used.

To implement full text searching today I would be strongly advising you towards Elasticsearch. There is a bundle for this already, the FOSElasticaBundle.

In this example, however, the implementation is basic enough to illustrate the point, but note that you can easily change the implementation without having to change how the filter parameter is passed by the URL, as we shall see momentarily.

To put this another way, the URL we for filtering may look something like this:

http://oursite.dev/app_dev.php/?filter=the

Which behind the scenes would do the following:

    public function cgetAction(Request $request)
    {
        $queryBuilder = $this->getBlogPostRepository()->createFindAllQuery();

        if ($request->query->getAlnum('filter')) {
            $queryBuilder
                ->where('bp.title LIKE :title')
                ->setParameter('title', '%' . $request->query->getAlnum('filter') . '%');
        }

        return $this->get('knp_paginator')->paginate(
            $queryBuilder->getQuery(), /* query NOT result */
            $request->query->getInt('page', 1), /*page number*/
            $request->query->getInt('limit', 10)/*limit per page*/
        );
    }

The 'interesting' part here being:

        if ($request->query->getAlnum('filter')) {
            $queryBuilder
                ->where('bp.title LIKE :title')
                ->setParameter('title', '%' . $request->query->getAlnum('filter') . '%');
        }

Let's quickly dissect this:

$queryBuilder = $this->getBlogPostRepository()->createFindAllQuery();

This goes off to our BlogPostRepository class and runs the createFindAllQuery method:

<?php

// /src/AppBundle/Entity/Repository/BlogPostRepository.php

namespace AppBundle\Entity\Repository;

use Doctrine\ORM\EntityRepository;

class BlogPostRepository extends EntityRepository
{
    public function createFindAllQuery()
    {
        return $this->_em->getRepository('AppBundle:BlogPost')->createQueryBuilder('bp');
    }

    // * snip *
}

As you can see, this will return a QueryBuilder object - not a query itself.

We can then use this QueryBuilder object again in our controller to add a where clause, only if the Request object contained a filter parameter:

        if ($request->query->getAlnum('filter')) {
            // do stuff here
        }

As I mentioned earlier, in this example we use a basic LIKE query, but this is where you could switch to any other implementation - and still the URL would remain the same.

At this stage we have all the required knowledge to implement this exact setup for our API. So let's get on with that in the very next video.