Reusing Queries with the Query Builder
Keep on Learning!
If you liked what you've learned so far, dive in! Subscribe to get access to this tutorial plus video, code and script downloads.
Enough with all this SQL stuff. Remember the query builder I was raving about earlier? I promised that one of its benefits is that, with a query builder, you can re-use parts of a query. But we don't have any of that right now.
Open up CategoryRepository
. We have three methods, and all of them repeat
the same leftJoin()
to cat.fortuneCookies
and the addSelect()
:
// ... lines 1 - 14 | |
public function findAllOrdered() | |
{ | |
$qb = $this->createQueryBuilder('cat') | |
// ... line 18 | |
->leftJoin('cat.fortuneCookies', 'fc') | |
->addSelect('fc'); | |
// ... lines 21 - 23 | |
} | |
// ... line 25 | |
public function search($term) | |
{ | |
return $this->createQueryBuilder('cat') | |
// ... lines 29 - 31 | |
->leftJoin('cat.fortuneCookies', 'fc') | |
->addSelect('fc') | |
// ... lines 34 - 36 | |
} | |
// ... line 38 | |
public function findWithFortunesJoin($id) | |
{ | |
return $this->createQueryBuilder('cat') | |
// ... line 42 | |
->leftJoin('cat.fortuneCookies', 'fc') | |
->addSelect('fc') | |
// ... lines 45 - 47 | |
} | |
// ... lines 49 - 50 |
Ah, duplication! When you see duplication like this - whether it's a WHERE, an ORDER BY or a JOIN - there's a simple solution. Just add a new private function and have it add this stuff to the query builder.
Query-modifying Functions
Create a private function called addFortuneCookieJoinAndSelect()
, because
that's what it's going to do! It'll accept a QueryBuilder
as an argument.
Our goal is to, well, add the join to that. So I'll copy the 2 pieces that
we want, add a $qb
, then paste it there. And just for convenience, let's
return this too:
// ... lines 1 - 52 | |
/** | |
* Joins over to cat.fortuneCookies AND selects its fields | |
* | |
* @param QueryBuilder $qb | |
* @return QueryBuilder | |
*/ | |
private function addFortuneCookieJoinAndSelect(QueryBuilder $qb) | |
{ | |
return $qb->leftJoin('cat.fortuneCookies', 'fc') | |
->addSelect('fc'); | |
} | |
// ... lines 64 - 66 |
So, our function takes in a QueryBuilder
, it modifies it, then it returns
it so we can make any more changes. I'll be a nice programmer and add some
PHPDoc.
Calling those Functions
The findAllOrdered()
function is the one that fuels the homepage. So let's
start here! Get rid of that duplicated leftJoin
and addSelect
. Instead,
just call $this->addFortuneCookieJoinAndSelect()
and pass it the $qb
.
So we create the query builder, do some things with it, but let our new
function take care of the join stuff.
// ... lines 1 - 15 | |
public function findAllOrdered() | |
{ | |
$qb = $this->createQueryBuilder('cat') | |
->addOrderBy('cat.name', 'ASC'); | |
$this->addFortuneCookieJoinAndSelect($qb); | |
$query = $qb->getQuery(); | |
return $query->execute(); | |
} | |
// ... lines 26 - 66 |
This should give us the exact same results. But you should never believe me, so let's go back and refresh the homepage. Yep, nice!
Now we get to celebrate by removing the rest of the duplication. So, addSelect
and leftJoin
should be gone. Instead of returning the result directly,
we need to get a QueryBuilder first. So put $qb =
in front and move the
getQuery()
stuff down and put the return
in front of it. In the middle,
call addFortuneCookieJoinAndSelect()
like before:
// ... lines 1 - 26 | |
public function search($term) | |
{ | |
$qb = $this->createQueryBuilder('cat') | |
->andWhere('cat.name LIKE :searchTerm | |
OR cat.iconKey LIKE :searchTerm | |
OR fc.fortune LIKE :searchTerm'); | |
$this->addFortuneCookieJoinAndSelect($qb); | |
return $qb | |
->setParameter('searchTerm', '%'.$term.'%') | |
->getQuery() | |
->execute(); | |
} | |
// ... lines 40 - 66 |
And one more time in findWithFortunesJoin()
. Remove the duplication, create
a $qb
variable, return the last part of the query, and stick our magic
line in the middle::
// ... lines 1 - 39 | |
public function findWithFortunesJoin($id) | |
{ | |
$qb = $this->createQueryBuilder('cat') | |
->andWhere('cat.id = :id'); | |
$this->addFortuneCookieJoinAndSelect($qb); | |
return $qb | |
->setParameter('id', $id) | |
->getQuery() | |
->getOneOrNullResult(); | |
} | |
// ... lines 52 - 66 |
Try it! Refresh and click into a category. It all works. And you know, I feel a lot better. If there's one things I don't want to duplicate, it's query logic. I hope this looks really obvious to you - it's just a simple coding technique. But it's kind of amazing, because it's not something you can do easily with string queries. And it can really save you if once you've got complex WHERE clauses that need to be re-used. You don't want to screw that stuff up.
Hello! I replaced the createQueryBuilder method in my repository class in order to return my own QueryBuilder object and add new methods to it:
Now in my repository class I can write:
Where $numberOfPayments and $totalAmount are other query builder objects I'm passing as parameters.
This seems to work OK, but I was wondering if it could be considered a good practice / idea :)