CODEWITHSUNDEEP
sqlsymfonyormdoctrine-orm
CôteViande
CôteViande

Reputation: 664

Doctrine include null on join

I am trying to create a query that will fetch every users, and, for those that have received a feedback, fetch their rating average as well.

After some research, I have crafted a query that will return all the users that have received a feedback and their respective rating. Which is a start. However I don't get the users with no feedback when I add the ->leftJoin('AppFeedbackBundle:Feedback', 'f', 'WITH', 'u.id = f.user')

$qb = $this->createQueryBuilder('u')
            ->select(array( 'u', 'AVG(r.ratings)' ))
            ->orderBy('u.lastLogin', 'DESC')
            ->setMaxResults($limit)
            ->leftJoin('AppFeedbackBundle:Feedback', 'f', 'WITH', 'u.id = f.user')
            ->join('f.rating', 'r')
            ->groupBy('u.id');

Do you know how I could get both the users with and without feedbacks, and associate said feedback to those who have one?

Remark

I would like to avoid reversing the ManyToOne relationship (feedback <-> user) in the User entity because I tend to fetch users all the time and I feel that I will end up having lots of feedbacks, hence I don't want to have those every time I get users, for performance reasons (please tell me if you don't think this performance argument is relevant or you think it does not matter much).

Note

The links between feedbacks, users and ratings are all in the feedback entity, like such:

/**
 * @ORM\ManyToOne(targetEntity="App\UserBundle\Entity\User", cascade={"persist", "remove"})
 */
private $user;

/**
 * @ORM\OneToOne(targetEntity="App\FeedbackBundle\Entity\Rating", cascade={"persist", "remove"})
 * @Assert\Valid()
 */
private $rating;

Upvotes: 2

Views: 1709

Answers (1)

Nawfal Serrar
Nawfal Serrar

Reputation: 2263

Left Join Rating too as all users who have feedback have rating but not the ones without feedback.

Upvotes: 2

Related Questions