Buy Access to Course
02.

The QueryBuilder

|

Share this awesome video!

|

It's really powerful to understand that DQL is ultimately what's being used behind the scenes in Doctrine. But most of the time, we're not going to build this DQL string by hand. Nope, we're going to use something called the "QueryBuilder". Ooooh.

Creating the QueryBuilder

Comment out the DQL. Let's rebuild this with the QueryBuilder. Start with $qb (for "QueryBuilder") = $this->createQueryBuilder(). Inside, say category.

81 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 17
class CategoryRepository extends ServiceEntityRepository
{
// ... lines 20 - 27
public function findAllOrdered(): array
{
// ... line 30
$qb = $this->createQueryBuilder('category')
// ... lines 32 - 35
}
// ... lines 37 - 79
}

Because we're inside CategoryRepository, when we say createQueryBuilder(), that automatically adds FROM App\Entity\Category and aliases it to category, since that's what we passed as the argument. This also selects everything by default. So... with just this, we've already recreated most of this query!

To add the next spot, you can chain off of this: ->addOrderBy() with category.name. Then I'll use this Criteria class ( hit "tab" to autocomplete that) followed by DESC. Or you could just put the string 'DESC': it's the same thing.

81 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 27
public function findAllOrdered(): array
{
// ... line 30
$qb = $this->createQueryBuilder('category')
->addOrderBy('category.name', Criteria::DESC);
// ... lines 33 - 35
}
// ... lines 37 - 81

Executing the QueryBuilder

QueryBuilder done! To execute it, we still need that Query object. Now we can get it with $qb->getQuery(). Internally, this should generate the exact same DQL as before, and I can prove it! Add a dd() with $query and, instead of saying ->getSQL(), say ->getDQL().

82 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 27
public function findAllOrdered(): array
{
// ... lines 30 - 32
$query = $qb->getQuery();
dd($query->getDQL());
// ... lines 35 - 36
}
// ... lines 38 - 82

When we try that... yeah! That is exactly what we wrote before! So, no surprise, if we remove that dd() and refresh... we're back to working! It's just that easy.

81 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 27
public function findAllOrdered(): array
{
// ... line 30
$qb = $this->createQueryBuilder('category')
->addOrderBy('category.name', Criteria::DESC);
$query = $qb->getQuery();
return $query->getResult();
}
// ... lines 37 - 81

Okay, we have the QueryBuilder basics down. Let's get more complex by adding andWhere() and orWhere() next.