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
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 getMostSeen() {
// query database and return results
}
}
But this is hard to debug. I prefer to have for each query, two functions: one that build the query (that can easily be tested and debugged) and another one that execute that query and return result:
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
The test class will be in the Tests directory of the bundle:
namespace Acme\BlogBundle\Tests\Controller;
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\Controller;
use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;
class PostRespositoryTest extends WebTestCase {
private $repo;
public function setUp() {
$kernel = static::createKernel();
$this->repo = $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. 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
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
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"
Feel free to ask questions right here
Thanks to the origin author here.