Symfony2 doctrine query to get data from 3 tables

Question

I am using Symfony Sonata User bundle for user management. I have created another entity as LoginHistory with OneToMany relationship between User and LoginHistory

I have to display the list of all users using KNP Paginator Bundle with their group name (normally belongs to one group only) with detail login history.

I have wrote this query, but it didn't get the group data of users.

 $query = $repository->createQueryBuilder('u')
                     ->join('PNCLogBundle:LoginHistory', 'log', 'WITH log.User = u.id')
                     ->innerJoin('u.groups', 'g')
                     ->andwhere('g.id = u.group')
                     ->where('u.roles LIKE :roles')
                     ->setParameter('roles', '%"ROLE_STAFF"%')
                     ->getQuery();

Answer 1

There are 2 types of JOINs: “Regular” Joins and “Fetch” Joins.

What you need is a fetch-join. You can read on this in the doctrine2 documentation. The solution is simple, just add g to your select clause in the query builder:

$query = $repository->createQueryBuilder('u')
                    ->select('u', 'g')
                    ->join('PNCLogBundle:LoginHistory', 'log', 'WITH log.User = u.id')
                    ->innerJoin('u.groups', 'g')
                    ->andwhere('g.id = u.group')
                    ->where('u.roles LIKE :roles')
                    ->setParameter('roles', '%"ROLE_STAFF"%')
                    ->getQuery();

You can also use the addSelect method:

$query = $repository->createQueryBuilder('u')
                    ->addSelect('g')
                    ->join('PNCLogBundle:LoginHistory', 'log', 'WITH log.User = u.id')
                    ->innerJoin('u.groups', 'g')
                    ->andwhere('g.id = u.group')
                    ->where('u.roles LIKE :roles')
                    ->setParameter('roles', '%"ROLE_STAFF"%')
                    ->getQuery();