Symfony Doctrine findBy and then map

Question

Basically I want to execute this mysql query with doctrine:

select distinct user_id from work_hour where project_id = ?;

But I don't know how I can do this with pretty Doctrine code. Is it possible to make it look like the following pseudo code or do I have to use the query builder?:

$project = new Project();
...
$entities = $em->getRepository('AppBundle:WorkHour')
    ->findByProject($project)->selectUser()->distinct();

Where $entities is an array of User objects

WorkHour and Project have a ManyToOne relation

WorkHour and User have a ManyToOne relation

Answer 1

You'll need to use a QueryBuilder for that, but that would still be quite a "pretty Doctrine code" and would still look quite like your pseudo-code.
Something like this should work:

$queryBuilder = $em->createQueryBuilder();

$query = queryBuilder
        ->select('u')
        ->distinct()
        ->from('AppBundle:User', 'u')
        ->join('AppBundle:WorkHour', 'wh')
        ->where('u.workHour = wh')
        ->andWhere('wh.project = :project')
        ->getQuery();

$query->setParameter(':project', $project);

$entities = $query->getResult();

Answer 2

public function findByProject($project)
{ 
    $qb = $this->getEntityManager()->createQueryBuilder('User');
    $qb
        ->select('User')
        ->from('Path\Bundle\Entity\User', 'User')
        ->join('Path\Bundle\Entity\WorkHour', 'wh',
            'WITH', 'User.workHour = wh')
        ->where('wh.project = :project'))
        ->distinct()
        ->setParameter('project', $project)
    ;

    $query = $qb->getQuery();

    return $query->getResult();
}

If you have a complicated query you should do it in QueryBuilder, it'll be more efficient.

http://doctrine-orm.readthedocs.org/en/latest/reference/query-builder.html

If you have complicated queries you shouldn't do it directly into the controller, it shouldn't know this logic, you have to do it in the repository and call it from there