Doctrine2 Symfony2 innerJoin QueryException Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

Question

As part of my website I'm trying to a create tagging (folksonomy) system using Symfony2 and Doctrine2.

I'm following the table and query examples in the document below to create my Doctrine Entities: http://dablog.ulcc.ac.uk/wp-content/uploads/2007/12/tagging_folksonomy.pdf

When I try to convert the MySQL queries (given in the document) to Doctrine Query Builder queries I get errors with the innerJoins. Example below:

MySQL query from the document:

SELECT tag_text
, COUNT(*) as num_tags
FROM Tag2Post t2p
INNER JOIN Tags t
ON t2p.tag_id = t.tag_id
GROUP BY tag_text;

My Doctrine Query Builder query:

$qb = $em->createQueryBuilder()
          ->select('t.tag_text, COUNT(*) as num_tags')
          ->from('CompanyWebsiteBundle:Tag2Post', 't2p')
          ->innerJoin('CompanyWebsiteBundle:Tags', 't', 'ON', 't2p.tag_id = t.id')
          ->groupBy('t.tag_text')
;
$tags = $qb->getQuery()->getResult();

Error message:

[2/2] QueryException: [Syntax Error] line 0, col 112: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'
[1/2] QueryException: SELECT t.tag_text, COUNT(*) as num_tags FROM CompanyWebsiteBundle:Tag2Post t2p INNER JOIN CompanyWebsiteBundle:Tag t ON t2p.tag_id = t.id GROUP BY t.tag_text

When I run the MySQL query directly on the database it works!

Solution

This should work. In DQL, the ON keyword is replaced by WITH.

$qb = $em->createQueryBuilder()
      ->select('t.tag_text, COUNT(*) as num_tags')
      ->from('CompanyWebsiteBundle:Tag2Post', 't2p')
      ->innerJoin('CompanyWebsiteBundle:Tags', 't', 'WITH', 't2p.tag_id = t.id')
      ->groupBy('t.tag_text')
;
$tags = $qb->getQuery()->getResult();

Also if you have properly configured entities, you should be able to leave out the , 'WITH', 't2p.tag_id = t.id' part as doctrine should automatically find the relations.

For example:

$qb = $em->createQueryBuilder()
      ->select('t.tag_text, COUNT(*) as num_tags')
      ->from('CompanyWebsiteBundle:Tag2Post', 't2p')
      ->innerJoin('t2p.tags', 't')
      ->groupBy('t.tag_text')
;
$tags = $qb->getQuery()->getResult();