How to make such a request in symfony?

Question

symfony

Hello! I have this sql query

select * from comment where id in (select child_comment_id from parent_child_comment where parent_comment_id = 12);

In the subquery, the foreign key is child_comment_id.
I started rewriting this request under symfony and there was a problem.
Here's what I got:

public function findChildsComment($id)
    {
        $entityManager = $this->getEntityManager();

        $query = $entityManager->createQuery("
        select comment from App\Entity\Comment comment 
        where comment.id in (select child.childCommentId from App\Entity\ParentChildComment child 
        where child.parentCommentId = :id)
        ")->setParameters([
            'id' => $id
        ]);

        return $query->getResult();
    }

And here is the error:

"[Semantical Error] line 0, col 99 near 'childCommentId': Error: Invalid PathExpression. Must be a StateFieldPathExpression.",

Of course, I realized that you need to remove the childCommentId in the subrequest and leave child. But then the wrong result is obtained. As far as I understand, instead of child, the id field is put, and it is necessary that there be a childCommentId field. How to do it?

Answer 1

And if you rewrite without in - through JOIN - won't it be easier?

SELECT DISTINCT comment.*
FROM comment
INNER JOIN parent_child_comment
  ON parent_child_comment.child_comment_id = comment.id
WHERE parent_child_comment.parent_comment_id = 12