CODEWITHSUNDEEP
doctrine-ormsymfonydql
Remi M
Remi M

Reputation: 436

Symfony One-To-Many, unidirectional with Join Table query

I have some One-To-Many, unidirectional with Join Table relationships in a Symfony App which I need to query and I can't figure out how to do that in DQL or Query Builder.

The Like entity doesn't have a comments property itself because it can be owned by a lot of different types of entities.

Basically I would need to translate something like this:

SELECT likes
FROM AppBundle:Standard\Like likes
INNER JOIN comment_like ON comment_like.like_id = likes.id
INNER JOIN comments ON comment_like.comment_id = comments.id
WHERE likes.created_by = :user_id
AND likes.active = 1
AND comments.id = :comment_id

I've already tried this but the join output is incorrect, it selects any active Like regardless of its association with the given comment

        $this->createQueryBuilder('l')
        ->select('l')
        ->innerJoin('AppBundle:Standard\Comment', 'c')
        ->where('l.owner = :user')
        ->andWhere('c = :comment')
        ->andWhere('l.active = 1')
        ->setParameter('user', $user)
        ->setParameter('comment', $comment)

Upvotes: 0

Views: 863

Answers (2)

Maksym Moskvychev
Maksym Moskvychev

Reputation: 1674

I see 2 options to resolve this:

  1. Make relation bi-directional
  2. Use SQL (native query) + ResultSetMapping.

For the last option, here is example of repository method (just checked that it works):

public function getLikes(Comment $comment, $user)
{
    $sql = '
        SELECT l.id, l.active, l.owner 
        FROM `like` l
        INNER JOIN comment_like ON l.id = comment_like.like_id
        WHERE comment_like.comment_id = :comment_id
            AND l.active = 1
            AND l.owner = :user_id
    ';
    $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->_em);
    $rsm->addRootEntityFromClassMetadata(Like::class, 'l');

    return $this->_em->createNativeQuery($sql, $rsm)
        ->setParameter('comment_id', $comment->getId())
        ->setParameter('user_id', $user)
        ->getResult();
}

PS: In case of Mysql, 'like' is reserved word. So, if one wants to have table with name 'like' - just surround name with backticks on definition:

 * @ORM\Table(name="`like`")

Upvotes: 1

Remi M
Remi M

Reputation: 436

I find the Symfony documentation very poor about unidirectional queries. Anyway I got it working by using DQL and sub-select on the owning entity, which is certainly not as fast. Any suggestion on how to improve that is more than welcomed! 

$em = $this->getEntityManager();
$query = $em->createQuery('
     SELECT l
     FROM AppBundle:Standard\Like l
     WHERE l.id IN (
         SELECT l2.id
         FROM AppBundle:Standard\Comment c
         JOIN c.likes l2
         WHERE c = :comment
         AND l2.owner = :user
         AND l2.active = 1 
     )'
)
->setParameter('user', $user)
->setParameter('comment', $comment)
;

Upvotes: 0

Related Questions