How to change Query to contain data from joined table?

Question

I have simple Entity:

id
username
guard

"guard" is id of another user from the same Entity. I have to render view with simple table:

username | name of guard
-------------------------
John     | Bob

I tried to do that by query:

$ur = $this->getDoctrine()->getRepository(User::class)->createQueryBuilder('u')
            ->leftJoin(User::class, 'u2', \Doctrine\ORM\Query\Expr\Join::WITH, 'u.guard = u2.id')
            ->getQuery()
            ->getResult();

but it gives me just id and username, no joined data. I know that entire query should be like:

SELECT 
  * 
FROM 
  user u0_ 
  LEFT JOIN user u1_ ON (u0_.guard = u1_.id)

but I can't find the way to implement that by QueryBuilder and then to access that in twig template.

Regards

Thanks. It returns now Array with User Objects and nulls. I see in profiler that query is good, if I test it in phpMyAdmin I get perfect result. But in Symfony results are different, not correct. — Lukaszy
[Semantical Error] I tried in two versions: (((1))) $ur = $this->getDoctrine()->getRepository(User::class) ->createQueryBuilder('u') ->addSelect('u2') ->leftJoin('u.guard', 'u2') (((2))) $ur = $this->getDoctrine()->getRepository(User::class) ->createQueryBuilder('u') ->addSelect('u2') ->leftJoin(User::class, 'u2', \Doctrine\ORM\Query\Expr\Join::WITH, 'u.guard = u2.id') — Lukaszy

Lukaszy Lukaszy · Accepted Answer · 2017-10-22T15:05:35

OK, I found out the mistakes in my code:

I tried to set that OneToOne realtion and that was small mistake, but I needed here ManyToOne.


    /**
     * Many Users have One Guard (User)
     * @ORM\ManyToOne(targetEntity="User")
     */
    private $guard = 0;

When I did that Symfony automatically force me to change my code and in column "guard" I have to insert User object.
After that I don't need join anymore - just select data from table and guard column includes User object which I can use in Twig, etc.


    namespace AppBundle\Entity;
    use Doctrine\ORM\EntityRepository;

    class UserRepository extends EntityRepository
    {
        public function findAllDB()
        {
            $qb = $this->createQueryBuilder('u');
            $query = $qb->getQuery();
            return $query->execute();
        }
    }

How to change Query to contain data from joined table?

1 Answers