Buy

Querying on a Relationship

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:

22 lines src/AppBundle/Repository/GenusNoteRepository.php
... 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:

22 lines src/AppBundle/Repository/GenusNoteRepository.php
... 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():

22 lines src/AppBundle/Repository/GenusNoteRepository.php
... 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":

101 lines src/AppBundle/Entity/GenusNote.php
... 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:Genus')->findAllRecentNotesForGenus() and pass it the $genus object from above:

120 lines src/AppBundle/Controller/GenusController.php
... 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:

42 lines app/Resources/views/genus/show.html.twig
... 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

Using the Relationship in the Query

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):

27 lines src/AppBundle/Repository/GenusNoteRepository.php
... 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')):

27 lines src/AppBundle/Repository/GenusNoteRepository.php
... 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.

Leave a comment!

  • 2016-11-23 weaverryan

    PhpStorm is so helpful to automatically add the use statement for me... that I don't mention it. Didn't mean to trip you up :)

    Happy Thanksgiving right back to you too!

  • 2016-11-22 Neal Ostrander

    Yep that was It I was wondering about the use statement the video didn't mention adding it. Thanks for the quick response Have a Happy Thanksgiving.
    Regards!

  • 2016-11-22 weaverryan

    Yo Neal!

    This is a tricky one - you're probably missing a use statement for Genus in the GenusNoteRepository. Because of that, PHP thinks that your Genus type-hint in that class must be for some (non-existent) AppBundle\Repository\Genus class (since it reverts to assuming that the class must live in the same namespace as that class). Then, you get this weird error where you were expecting AppBundle\Repository\Genus (non-existent class) but were passed AppBundle\Entity\Genus (which is correct).

    Add the use statement and you're good! But remember this error - it's a really tough one to track down until you're trained to spot it.

    Cheers!

  • 2016-11-22 Neal Ostrander

    When setting up the GenusNoteRepository I get this error when trying to run. "Catchable Fatal Error: Argument 1 passed to AppBundle\Repository\GenusNoteRepository::findAllRecentNotesForGenus() must be an instance of AppBundle\Repository\Genus, instance of AppBundle\Entity\Genus given, called in /home/neal/Project/aqua_note/src/AppBundle/Controller/GenusController.php on line 73 and defined" here is the code that I have in GenusController showAction. Probably just a typo but I'm finding it any help would be appreciated.
    $em = $this->getDoctrine()->getManager();
    $genus = $em->getRepository('AppBundle:Genus')
    ->findOneBy(['name' => $genusName]);

    if (!$genus) {
    throw $this->createNotFoundException('No genus found');
    }

    $recentNotes = $em->getRepository('AppBundle:GenusNote')
    ->findAllRecentNotesForGenus($genus);

  • 2016-07-06 weaverryan

    Boom! Glad you got it figured out, and thanks for sharing your answer!

    That *other* syntax (that didn't originally work), *is* valid, but you just have to do a lot more work (I believe the first "sites" would need to be the full class name for your Site entity). I see people doing this sometimes, but except for really advanced scenarios, it just seems like a lot more work :). Glad you got the "better" version.

    Cheers!

  • 2016-07-06 the_nuts

    Solved:


    public function findAllByUser(User $user){
    return $this->createQueryBuilder('link')
    ->join('link.site', 's')
    ->andWhere('s.user=:user')
    ->setParameter('user', $user)
    ->getQuery()
    ->execute();
    }
  • 2016-07-06 the_nuts

    Hello,
    how can I query on a manytotone relation?

    I have 3 entities:
    user, site, link. Each user can have more sites, and each site can have more links.
    I want to get all the links for the current logged in user.

    I tried this:


    public function findAllByUser(User $user){
    return $this->createQueryBuilder('link')
    ->join('sites', 's', 'ON', 'link.site_id=s.id')
    ->andWhere('s.user=:user')
    ->setParameter('user', $user)
    ->getQuery()
    ->execute();
    }

    but I get QueryException: [Semantical Error] line 0, col 72 near 'sites s ON link.site_id=s.id': Error: Class 'sites' is not defined.

    If I write `join('Site')`, it says the class `Site` is not defined (but it does exist an entity Site).
    Could you please help me?

  • 2016-05-07 weaverryan

    Where's the error coming from - inside your controller? It looks like you may just have a typo on your controller when you use the service. It should look something like the controller code on this page: https://knpuniversity.com/scre...


    $markdownTransformer = $this->get('app.markdown_transformer');

    I hope that helps!

  • 2016-05-06 Stéphane

    Thank for you reply. I add this statement but now there is another fatal error :

    Fatal error: Call to undefined method AppBundle\Service\MarkdownTransformer::get()

    You have some idea ?

  • 2016-05-05 weaverryan

    Yes, that's the right use statement! This needs to go in your MarkdownTransformer class - it's needed there because if the type-hint for MarkdownParserInterface that you have in the __construct() method :)

  • 2016-05-05 Stéphane

    Hi Ryan,

    In which file I have to add the use statement ?

    This use statement is correct ?
    use Knp\Bundle\MarkdownBundle\MarkdownParserInterface;

    Thank

  • 2016-05-04 weaverryan

    I think you're just missing a use statement for that MarkdownParserInterface! Add that, and I *bet* you'll be good :)

  • 2016-05-04 Stéphane

    Hi Ryan,

    Thank for you reply. No problem. I update your bundle but the error is still here :

    Type error: Argument 1 passed to
    AppBundle\Service\MarkdownTransformer::__construct() must be an instance
    of AppBundle\Service\MarkdownParserInterface, instance of
    Knp\Bundle\MarkdownBundle\Parser\Preset\Max given, called in
    /var/www/aqua_note/var/cache/dev/appDevDebugProjectContainer.php on line
    283

    I try to check my code.

  • 2016-05-04 weaverryan

    Hi Stéphane!

    Ah, this is due to a big that was introduced in the MarkdownBundle! And actually, *I* am the one who introduced this bug!!! I just fixed it and tagged a new release of that bundle - 1.5.1 - https://github.com/KnpLabs/Knp....

    If you run "composer update knplabs/knp-markdown-bundle", you will get the new version and it should be fixed.

    Sorry about that!

  • 2016-05-04 Stéphane

    Hello,
    Thank for all tutorials about Symfony. Very good staff !

    I try to do this one and I have this error after add the new method findAllRecentNotesForGenus() inti the controller

    Type error: Argument 1 passed to
    AppBundle\Service\MarkdownTransformer::__construct() must be an instance
    of AppBundle\Service\MarkdownParserInterface, instance of
    Knp\Bundle\MarkdownBundle\Parser\Preset\Max given, called in
    /var/www/aqua_note/var/cache/dev/appDevDebugProjectContainer.php on line
    282

    500 Internal Server Error - FatalThrowableError

    You know what is the problem ?

    Cheer.
    Stéphane