Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Querying on a Relationship

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.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

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

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

... 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.

Leave a comment!

32
Login or Register to join the conversation
Neal O. Avatar
Neal O. Avatar Neal O. | posted 4 years ago

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

1 Reply

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!

2 Reply
Neal O. Avatar

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!

Reply

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!

1 Reply
Default user avatar

I'm stuck with this error too.

When I declare the function findAllRecentNotesForGenus(Genus), the autocomplete in PhP-Storm is not proposing anything except what it finds in AppBundle\Repository and AppwBundle\Controller

If I ignore the autocomplete and just enter 'Genus', it obviously doesnt add the correct "use" statement.

This means that when I refresh the page.... big error!

So, what is the use statement I should be using?

Reply
Default user avatar

Pah! Just ignore me....

use AppBundle\Entity\Genus

Yes... I know... you already answered that!

Reply

haha, we don't like to ignore our users :P
We're glad that you could fix your problem

Cheers!

Reply
Default user avatar
Default user avatar Brady Maxwell | posted 4 years ago

After refreshing at 1:38 in video I get the following error.
Undefined method 'findAllRecentNotesForGenus'. The method name must start with either findBy or findOneBy!

1 Reply
Default user avatar

The issue for me was cache. I got the same error, but it disappeared after clearing cache and the count was then 100.

Reply

Hey Brady,

Do you add @ORM\Entity(repositoryClass="AppBundle\Repository\GenusNoteRepository") annotation above the GenusNote entity? If yes, could you show me the line where you call this method with some context?

Cheers!

Reply
Default user avatar

I'm still getting this same error and I've checked against the downloaded project files and couldn't find a typo. I found a mention of a solution by way of XML files (something about Doctrine ignoring the annotation for anything after the first Entity), but I haven't found an example of actual code. Also I can't seem to find any record of this as a Symfony or Doctrine bug - surely I'm not the only one affected. Any tips? By the way, these tutorial series are fantastic!

Reply

Hi Grace!

We can definitely figure it out! First, you did good research :). There are only 2 possible problems:

1) You have a typo when using the method findAllRecentNotesForGenus or in the method name in your repository class. Triple-check that these are identical.

2) (this is the more likely cause) Doctrine is (for some reason) not seeing your @ORM\Entity(repositoryClass="AppBundle\Repository\GenusNoteRepository") annotation above your class. If Doctrine doesn't see this (specifically, the repositoryClass part), then it will use the generic EntityRepository class instead of your custom class (which obviously doesn't have your custom method, hence the error). Of course, the question is... why? Here's a few things to try:

A) Make sure that your comment above the entity class starts with /**, not /* (there must be 2 starts)

B) Did you do any "reverse engineering" of your database (if you don't know what I'm talking about, then you didn't, great!). If you have any XML mapping files at *all*, then Doctrine will try to use those and will ignore your annotations.

C) Try putting a typo in your annotations and see if you get an error. For example, remove the @Entity line entirely. Can you still make simple queries to your entity (e.g. using findAll()). Or, try making a typo in the class name for repositoryClass. Do you get an error? Or does it happily still try to use your repository. This part is to try to see if Doctrine is (for some reason) ignoring your entities.

Let me know what you find out!

Cheers!

Reply
Default user avatar

Hello Ryan, i think that i know what causes the problem. At the beginning when i create the file "GenusNoteRepository.php" instead of create it from a php file i did it from a php class. And then, when i worked with it, i get exactly the error "Undefined method 'findAllRecentNotesForGenus'. The method name must start with either findBy or findOneBy!"
So after i search for it too much and i created in the "Repository" directory a php file and not a php class that time from scratch. After this it worked perfect!
I don t kknow why it happened because now i m starting with all these, but i hope that i helped a little bit!

Also, i have to mention that you re making an excellent work with this tutorial! It s great!!!!

1 Reply
Default user avatar

I did the same thing too, not sure if this is the culprit!

1 Reply

Hey argy_13 ,

Probably you have letter case problems in your project, i.e. namespace do not match the path to the file in your file structure. It difficult to say what exactly caused this error. Or maybe you just needed to clear the cache. Anyway, I'm glad you nailed it, good work!

Cheers!

1 Reply
Default user avatar

I ended up following along with the entire tutorial series again up until this point and the only thing I can think of is that I must have missed a few bits here and there. Everything works great now! I will say that if anyone else has these issues, have the listAction page AND the showAction page pulled up in tabs, then refresh to make sure one or the other didn't break. It helped that I got through everything in one day rather than stopping and starting over a few days. On to the next video!! :)

Reply

Hey Grace!

I'm glad you could fix your problem and you are enjoying our tutorials :)
Keep going man, cheers!

Reply
Default user avatar
Default user avatar Brady Maxwell | victor | posted 4 years ago

That was the second this I checked after my use statements. They all looked correct. However I decided to continue on and cut and pasted code form the download. That fixed it so somewhere i had a typo. :) thank you for the response.

Reply

Great! I'm glad you got it working ;)

Reply
Trafficmanagertech Avatar
Trafficmanagertech Avatar Trafficmanagertech | posted 4 years ago

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?

Reply
Trafficmanagertech Avatar
Trafficmanagertech Avatar Trafficmanagertech | Trafficmanagertech | posted 4 years ago

Solved:


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

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!

1 Reply
Richard Avatar
Richard Avatar Richard | posted 4 years ago

In the accompanying script $em->getRepository('AppBundle:Genus') should be $em->getRepository('AppBundle:GenusNote') I think.

Reply

Hey maxii123 ,

Nice catch! Thank you for reporting this issue, it's fixed in https://github.com/knpunive...

Cheers!

Reply

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

Reply

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/....

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

Sorry about that!

Reply

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.

Reply

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

Reply

Hi Ryan,

In which file I have to add the use statement ?

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

Thank

Reply

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

Reply

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 ?

Reply

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/s...


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

I hope that helps!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

What PHP libraries does this tutorial use?

// 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
    }
}