Symfony2 join query

Question

I have a table of videos and in that table I have field comment which contains id of comment in other table, now I used join query to get that in one query, but how do I get that comment?

Here is my code:

$Actions = $this->EntityManager()->getRepository('AppBundle:Video')
                                    ->createQueryBuilder('V')
                                    ->join('AppBundle:VideoComment', 'VC')
                                    ->where('V.videoId = :VideoID')
                                    ->andWhere('VC.videoId = :VideoID')
                                    ->setParameter('VideoID', $VideoID)
                                    ->getQuery()
                                    ->getResult();

How do I get the actual comment from that joined entity?

Answer 1

Here is one proposal:

<?php
namespace You\AppBundle\Repository; // You is your vendor name, AppBundle is your bundle

use Doctrine\ORM\EntityRepository;

class VideoCommentRepository extends EntityRepository
{
    public function getVideoComment($VideoId)
    {
        $query = $this->getEntityManager()->createQuery(
            'SELECT v FROM YouAppBundle:Video v LEFT JOIN v.comment c
             WHERE v.id = :id'
        )->setParameter('id', $VideoId);

        return $query->getResult();
    }
}

As you said you have a table 'video' and in that table there is a field 'comment' that contains the IDs of the comments. I suppose you have 'oneToMany' relation from 'video' to 'comment'. With this simple query you should be able to get all comments for a given VideoID. I didn't test this, but I think it should work. Try it out and adapt it as needed.

Answer 2

You can do what @cezar said earlier but with one little change: you have to define field to retrieve related entries from comments table. So, your query might look like this:

$em = $this->get('doctrine.orm.entity_manager');
$videos = $em->createQuery('select v 
                            from YourBundle:Video v 
                            left join YourBundle:Comment c 
                            where v.comment = c.id')
             ->getResult();

or you can do the similar stuff using query builder:

$videos = $em->createQueryBuilder('v')
             ->add('select', 'v, c')
             ->add('from', 'YourBundle:Video v')
             ->leftJoin('YourBundle:Comment', 'c')
             ->where('v.comment = c.id')
             ... // some other conditions if you need
             ->getQuery()
             ->getResult();

Both cases I described account for that Video and Comment entity might not be in formal relations (I mean their relations might not be described in your doctrine/orm file).