Unit Testing Symfony2 Repositories

Oct 8th, 2011

Unit testing of Symfony2 repositories are harder as they require a connection to the database. Here is a step-by-step tutorial showing how to do it with PHPUnit.

Here is the workflow I have to add a new function in an entity repository. Let’s say I have a blog and I want to get the most seen posts by counting the visits for each post. Here is the workflow I have:

Write the functions structure in the repository
Write the test structure test class
Write the DQL query with the console
Write the test in the test class
Write the query in the repository

1. Write the functions structure in the repository

The usual way to do add a query in a repository to add a new function that query the database and return the results:

namespace Acme\BlogBundle\Repository;
use Doctrine\ORM\EntityRepository;
class PostRepository extends EntityRepository {
    public function qbMostSeen() {
        return $this
            ->createQueryBuilder('p')
            -> // ...
    }
    
    public function getMostSeen() {
        return $this
            ->qbMostSeen()
            ->getQuery()
            ->getResult()
        ;
    }
}

2. Write the test structure test class

The test class will be in the Tests directory of the bundle:

namespace Acme\BlogBundle\Tests\Repository;
use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;

class PostRespositoryTest extends WebTestCase {
    // ...
}

But we want to be able to instantiate PostRepository and for that we need the Entity Manager and a kernel (that we don’t have by default). So let’s add setUp() that will be executed before the tests:

namespace Acme\BlogBundle\Tests\Repository;
use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;

class PostRespositoryTest extends WebTestCase {
    private $repo;

    public function setUp() {
        $kernel = static::createKernel();
        $kernel->boot();
        $this->repo = $kernel
            ->getContainer()
            ->get('doctrine.orm.entity_manager')
            ->getRepository('AcmeBlogBundle:Post')
        ;
    }
}

We add our tests:

on the query itself: here we compare the query DQL to the DQL we are going to write
on the results: here we check that we have some results

public function testMostSeen()
{
    $this->assertEquals(
        $this->repo->qbMostSeen()->getDql(),
        "..." // the DQL query
    );

    $this->assertNotEquals(0, count($this->repo->getMostSeen()));
}

You can add your tests on either the query or the results.

3. Write the DQL query with the console

Let’s write the DQL step by step testing each step with the console. Here, we get all the posts:

app/console doctrine:query:dql --max-result=5 "SELECT p.title FROM Acme\BlogBundle\Entity\Post p"

then we add one by one parts of our query, testing on our database to check the result every time. A left join to get all the visits:

SELECT p.title, v.id
FROM Acme\BlogBundle\Entity\Post p
LEFT JOIN p.visits v

then we add the visit count:

SELECT p.title, COUNT(v) AS visit_count
FROM Acme\BlogBundle\Entity\Post p
LEFT JOIN p.visits v
GROUP BY p.title

and we order by visit count:

SELECT p.title, COUNT(v) AS visit_count
FROM Acme\BlogBundle\Entity\Post p
LEFT JOIN p.visits v
GROUP BY p.title
ORDER BY visit_count DESC

Now that we are happy with the query, we can finish to write our tests.

4. Write the test in the test class

Just insert the DQL in the test:

$this->assertEquals(
    $this->repo->qbMostSeen()->getDql(),
    "SELECT p.title, COUNT(v) AS visit_count FROM Acme\BlogBundle\Entity\Post p LEFT JOIN p.visits v GROUP BY p.title ORDER BY visit_count DESC"
);

5. Write the query in the repository

Finally, write the query until PHPUnit is green!

namespace Acme\BlogBundle\Repository;
use Doctrine\ORM\EntityRepository;

class PostRepository extends EntityRepository {
    public function qbMostSeen() {
        return $this
            ->createQueryBuilder('p')
            ->select('p.title', 'COUNT(v) AS visit_counts')
            ->leftJoin('p.visits', 'v')
            ->orderBy('visit_counts', 'DESC')
        ;
    }

    public function getMostSeen() {
        return $this
            ->qbMostSeen()
            ->getQuery()
            ->getResult()
        ;
    }
}

Pro tip

You might want to add the alias dql to your shell environment (.bash_profile, .zsh/zshaliases or whatever):

alias dql="app/console doctrine:query:dql"

so you can just run in your shell:

dql "SELECT p.title FROM Acme\BlogBundle\Entity\Post p"

🌱 Quentin Pleplé