How to adjust Inner Join in Querybuilder?

Question

Running this query directly in SQL works

SELECT T2.id, T2.name, T2.description, T2.version, T2.img_thumb, T2.interval, T2.date_created, T1.id_app, T1.is_active 
FROM Categoria T1 
INNER JOIN DetalhesCategoria T2 on T1.id_app = T2.id 
WHERE T1.fk_user = 4;

However I am doubtful how to execute this query by Querybuilder?

It follows how this currently:

$qb = $this->entityManager->createQueryBuilder();
$qb->select('T2.id, T2.name, T2.description, T2.qtde_users, T2.version, T2.img_thumb, T2.method_payment, T2.interval, T2.date_created, T1.id_app, T1.is_active') 
            ->from('Application\\Entity\\Categoria', 'T1') 
            ->innerJoin('Application\\Entity\\DetalhesCategoria', 'T2', 'T1.id_app = T2.id') 
            ->where('T1.fk_user = ' . $idCurrentUser);
        $results = $qb->getQuery()->getResult();

I doubt if the innerJoin is being passed correctly.

Answer 1

The SQL under discussion can be converted into the following Doctrine Querybuilder:

$qb = $this->entityManager->createQueryBuilder('c');
$qb->select(['c', 'dc'])
   ->from(Categoria::class, 'c')
   ->innerJoin(DetalhesCategoria::class, 'dc', Expr\Join::WITH, 'c.id_app = dc.id') 
   ->where('c.fk_user = :fk_user')
   ->setParameter('fk_user', $idCurrentUser);
$results = $qb->getQuery()->getResult();

The method innerJoin() has the following parameters (excerpt from the official documentation):

// Example - $qb->innerJoin('u.Group', 'g', Expr\Join::WITH, $qb->expr()->eq('u.status_id', '?1'))
// Example - $qb->innerJoin('u.Group', 'g', 'WITH', 'u.status = ?1')
// Example - $qb->innerJoin('u.Group', 'g', 'WITH', 'u.status = ?1', 'g.id')
public function innerJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null);

Reference: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/query-builder.html