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

You need to use the query builder. See http://stackoverflow.com/questions/7188219/how-to-select-distinct-query-using-symfony2-doctrine-query-builder — redbirdo, Jun 11 '15 at 10:34

MikO · Accepted Answer · 2015-06-11T15:21:42.863

3

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();

edited Jun 11 '15 at 15:21

answered Jun 11 '15 at 10:57

MikO

18,243
12
77
109

I like your answer more than the answer above, but I cant bring it to work... For me it looks like it has a problem with `wh.user` because it crashed with `QueryException: [Semantical Error] line 0, col 19 near 'user FROM AppBundle\Entity\WorkHour': Error: Invalid PathExpression. Must be a StateFieldPathExpression.`. Full Query: `QueryException: SELECT DISTINCT wh.user FROM AppBundle\Entity\WorkHour wh` (I commented the project part out for debugging) – Jonas Felber Jun 11 '15 at 12:25
@JonasFelber sorry I had a mistake in the code - I'm not trying the code, just writing it :) I edited the answer, I'm pretty sure it should work – MikO Jun 11 '15 at 14:43

Fernando Caraballo · Answer 2 · 2015-06-11T11:07:24.360

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

Symfony Doctrine findBy and then map

2 Answers2