How to properly organize complex queries in doctrine ?

Question

symfony

I have a query like this to get related posts by tag

SELECT Post.title, Post_Tags.article_id, count(*) count_alike_tags
                    FROM `article_tags` Post_Tags
                        LEFT JOIN `Article` Post
                        ON Post_Tags.article_id = Post.id
                        WHERE tag_id IN (SELECT tag_id FROM article_tags Post_Tags_Alias
                     WHERE Post_Tags_Alias.article_id = :id AND Post_Tags.article_id <>
                        Post_Tags_Alias.article_id)
                        GROUP BY Post_Tags.article_id
                        ORDER BY count_alike_tags DESC;

I dug through the docs and condensed it into a doctrine like this:

$em = $this->getDoctrine()->getEntityManager();
        $connection = $em->getConnection();
        $statement = $connection->prepare("
                SELECT Post.title, Post_Tags.article_id, count(*) count_alike_tags
                    FROM `article_tags` Post_Tags
                        LEFT JOIN `Article` Post
                        ON Post_Tags.article_id = Post.id
                        WHERE tag_id IN (SELECT tag_id FROM article_tags Post_Tags_Alias
                     WHERE Post_Tags_Alias.article_id = :id AND Post_Tags.article_id <>
                        Post_Tags_Alias.article_id)
                        GROUP BY Post_Tags.article_id
                        ORDER BY count_alike_tags DESC;
        ");
        $statement->bindValue('id', 1);
        $statement->execute();
        $results = $statement->fetchAll();

There are 2 questions here. How to organize it correctly, well, let's say maybe there is a more elegant option?
As I understand it, writing such things and generally the doctrine in the controller is not good, so you need to create a new class in Entity, for example PostManager and write everything there. Did I understand correctly?

Answer 1

Use the queryBuilder in conjunction with the Entities you received.
Take out the big logic in services. For complex requests, create Custom Repositories and access them in the controller through simpler calls. For native queries, then it’s better not to use Doctrine at all, but to use php pdo.