If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
We need to create a query that returns the GenusNotes that belong to a specific
Genus
and are less than 3 months old. To keep things organize, custom queries
to the GenusNote
table should live in a GenusNoteRepository
. Ah, but we don't
have one yet! No problem: copy GenusRepository.php
to GenusNoteRepository.php
,
rename the class and clear it out:
... lines 1 - 2 | |
namespace AppBundle\Repository; | |
... lines 4 - 6 | |
use Doctrine\ORM\EntityRepository; | |
class GenusNoteRepository extends EntityRepository | |
{ | |
... lines 11 - 20 | |
} |
Add a new public function findAllRecentNotesForGenus()
and give this a Genus
argument:
... lines 1 - 8 | |
class GenusNoteRepository extends EntityRepository | |
{ | |
/** | |
* @param Genus $genus | |
* @return GenusNote[] | |
*/ | |
public function findAllRecentNotesForGenus(Genus $genus) | |
{ | |
... lines 17 - 19 | |
} | |
} |
Excellent! And just like before - start with return $this->createQueryBuilder()
with genus_note
as a query alias. For now, don't add anything else: finish with
the standard ->getQuery()
and ->execute()
:
... lines 1 - 8 | |
class GenusNoteRepository extends EntityRepository | |
{ | |
... lines 11 - 14 | |
public function findAllRecentNotesForGenus(Genus $genus) | |
{ | |
return $this->createQueryBuilder('genus_note') | |
->getQuery() | |
->execute(); | |
} | |
} |
Doctrine doesn't know about this new repository class yet, so go tell it! In
GenusNote
, find @ORM\Entity
and add repositoryClass="AppBundle\Repository\GenusNoteRepository"
:
... lines 1 - 6 | |
/** | |
* @ORM\Entity(repositoryClass="AppBundle\Repository\GenusNoteRepository") | |
* @ORM\Table(name="genus_note") | |
*/ | |
class GenusNote | |
{ | |
... lines 13 - 99 | |
} |
Finally, use the new method in GenusController
-
$recentNotes = $em->getRepository('AppBundle:GenusNote')->findAllRecentNotesForGenus()
and pass it the $genus
object from above:
... lines 1 - 12 | |
class GenusController extends Controller | |
{ | |
... lines 15 - 57 | |
public function showAction($genusName) | |
{ | |
... lines 60 - 85 | |
$recentNotes = $em->getRepository('AppBundle:GenusNote') | |
->findAllRecentNotesForGenus($genus); | |
... lines 88 - 92 | |
} | |
... lines 94 - 118 | |
} |
Obviously, we're not done yet - but it should at least not break. Refresh. Ok,
100 recent comments - that's perfect: it's returning everything. Oh, you know
what isn't perfect? My lame typo - change that to the word Recent
. Embarrassing
for me:
... lines 1 - 4 | |
{% block body %} | |
<h2 class="genus-name">{{ genus.name }}</h2> | |
<div class="sea-creature-container"> | |
<div class="genus-photo"></div> | |
<div class="genus-details"> | |
<dl class="genus-details-list"> | |
... lines 12 - 17 | |
<dt>Recent Notes</dt> | |
... line 19 | |
</dl> | |
</div> | |
</div> | |
<div id="js-notes-wrapper"></div> | |
{% endblock %} | |
... lines 25 - 42 |
Head back to the repository. This query is pretty simple actually: add
an ->andWhere('genus_note.genus = :genus')
. Then, fill in :genus
with
->setParameter('genus', $genus)
:
... lines 1 - 8 | |
class GenusNoteRepository extends EntityRepository | |
{ | |
... lines 11 - 14 | |
public function findAllRecentNotesForGenus(Genus $genus) | |
{ | |
return $this->createQueryBuilder('genus_note') | |
->andWhere('genus_note.genus = :genus') | |
->setParameter('genus', $genus) | |
... lines 20 - 22 | |
->getQuery() | |
->execute(); | |
} | |
} |
This a simple query - equivalent to SELECT * FROM genus_note WHERE genus_id =
some number. The only tricky part is that the andWhere()
is done on
the genus
property - not the genus_id
column: you always reference
property names with Doctrine.
Finish this with another andWhere('genus_note.createdAt > :recentDate')
and
->setParameter('recentDate', new \DateTime('-3 months'))
:
... lines 1 - 8 | |
class GenusNoteRepository extends EntityRepository | |
{ | |
... lines 11 - 14 | |
public function findAllRecentNotesForGenus(Genus $genus) | |
{ | |
return $this->createQueryBuilder('genus_note') | |
->andWhere('genus_note.genus = :genus') | |
->setParameter('genus', $genus) | |
->andWhere('genus_note.createdAt > :recentDate') | |
->setParameter('recentDate', new \DateTime('-3 months')) | |
... line 22 | |
->getQuery() | |
->execute(); | |
} | |
} |
Perfect! Go back and try it - the count should go back to 6. There we go! But now, instead of fetching all the notes just to count some of them, we're only querying for the ones we need. And, Doctrine loves returning objects, but you could make this even faster by returning only the count from the query, instead of the objects. Don't optimize too early - but when you're ready, we cover that in our Going Pro with Doctrine Queries.
// composer.json
{
"require": {
"php": ">=5.5.9",
"symfony/symfony": "3.1.*", // v3.1.4
"doctrine/orm": "^2.5", // v2.7.2
"doctrine/doctrine-bundle": "^1.6", // 1.6.4
"doctrine/doctrine-cache-bundle": "^1.2", // 1.3.0
"symfony/swiftmailer-bundle": "^2.3", // v2.3.11
"symfony/monolog-bundle": "^2.8", // 2.11.1
"symfony/polyfill-apcu": "^1.0", // v1.2.0
"sensio/distribution-bundle": "^5.0", // v5.0.22
"sensio/framework-extra-bundle": "^3.0.2", // v3.0.16
"incenteev/composer-parameter-handler": "^2.0", // v2.1.2
"composer/package-versions-deprecated": "^1.11", // 1.11.99
"knplabs/knp-markdown-bundle": "^1.4", // 1.4.2
"doctrine/doctrine-migrations-bundle": "^1.1" // 1.1.1
},
"require-dev": {
"sensio/generator-bundle": "^3.0", // v3.0.7
"symfony/phpunit-bridge": "^3.0", // v3.1.3
"nelmio/alice": "^2.1", // 2.1.4
"doctrine/doctrine-fixtures-bundle": "^2.3" // 2.3.0
}
}