Doctrine2 how to make a query correctly?

Question

symfony

There are tags on the site. They are implemented via a link table (post_id, tag_id).
You need to get all the news with the most repetitive tags.
I have this code:

SELECT Post.id, Post.title
        FROM news_post Post
        INNER JOIN `news_post_tags` Post_Tags
        ON  Post.id = Post_Tags.post_id
        WHERE Post_Tags.tag_id IN (1, 2, 3, 4, 5, 6, 7) 
        GROUP BY Post_Tags.post_id 
        ORDER BY COUNT( Post_Tags.tag_id ) DESC

it works great and now it needs to be written with doctrine.
I started writing:

return $this->getEntityManager()
            ->createQuery("SELECT Post "
                . "FROM IlichNewsBundle:Post Post "
                . "INNER JOIN Post.tags Tags "
            )
            ->setFirstResult(0)->setMaxResults(50)->getResult();

Doctrine generates something like this:

SELECT 
  n0_.id AS id0, 
  n0_.slug AS slug1, 
  ... 
FROM 
  news_post n0_ 
  INNER JOIN news_post_tags n2_ ON n0_.id = n2_.post_id 
  INNER JOIN Tag t1_ ON t1_.id = n2_.tag_id 
LIMIT 
  50 OFFSET 0

So I need to access the table
INNER JOIN news_post_tags n2_ ON n0_.id = n2_.post_id That
is, do something like this:

return $this->getEntityManager()
            ->createQuery("SELECT Post "
                . "FROM IlichNewsBundle:Post Post "
                . "INNER JOIN Post.tags Tags "
        GROUP BY n0_.post_id 
        ORDER BY COUNT( n0_.tag_id ) DESC;
            )
            ->setFirstResult(0)->setMaxResults(50)->getResult();

how can I set an alias for this table news_post_tags ?

Answer 1

doctrine (in dql) can't sort by calculated field, either native query or sqlWalker