1

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?

Improve this question
4
  • If you have the relation on the Video entity, you can simply navigate it (something like getComments()) Commented Sep 22, 2014 at 16:26
  • And if i dont have relation? Commented Sep 22, 2014 at 16:43
  • Then you won't get it. Plus your query is all messed up. DQL is not SQL. Take a look at the examples in the documentation. Commented Sep 22, 2014 at 18:21
  • @SilvioMarijic here you said 'I have a table of videos and in that table I have field comment which contains id of comment in other table', that is the relation. Commented Sep 22, 2014 at 20:42

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.