Doctrine querybuilder complex rule

Question

Need help with complex query. Server receive some params for query, than that query is serializing to json. I need to add avg rating for each quest(so the field could be accessible from json.item[i].rating) and sort them with rating.

And one more thing: is there better way to keep average rating in db? i feel that calculating rating all the time is not good idea.

PS: btw switch case is faster for null check?

//some params

$repository = $this->getDoctrine()->getRepository('AppBundle:Quest');
        $qb = $repository->createQueryBuilder('q')
            ->innerJoin('q.city', 'c')
            ->where('c.id = :city_id')
            ->setParameter('city_id', $cityId);

        if($minPlayers != null){
            $qb ->andWhere('q.minplayers >= :minplayers')
                ->setParameter('minplayers', $minPlayers);
        }
        if($maxPlayers != null){
            $qb ->andWhere('q.maxplayers <= :maxplayers')
                ->setParameter('maxplayers', $maxPlayers);
        }
        if($tagsId != null){
            $qb ->innerJoin('q.tags','t')
                ->andWhere('t.id IN (:tags)')
                ->setParameter(':tags', implode(",", $tagsId));
        }
        if($organizerId != null){
            $qb ->andWhere('q.organizer = :organizer_id')
                ->setParameter('organizer_id', $organizerId);
        }
        if($ispremium != null){
            $qb ->andWhere('q.ispremium = :isPremium')
                ->setParameter('isPremium', $ispremium)
                ->addOrderBy('q.premiumorder', 'ASC');
        }
        $qb ->setFirstResult($FirstResult)
            ->setMaxResults($MaxResults);


        $query = $qb->getQuery();

        $paginator = new Paginator($query, $fetchJoinCollection = true);
        $c = count($paginator);

        $result = $query->getResult();

        $serializer = $this->get('jms_serializer');
        $data = $serializer->serialize($result, 'json',
            SerializationContext::create()
                ->enableMaxDepthChecks()
                ->setGroups(array('questSearch'))
        );

        $json ='{"total_count":'.$c.',"items":'.$data.',"amountOfelems":'.$amountOfelems.',"citySlug":'.json_encode($citySlug).',"userId":'.json_encode($userId).'}';

        return new JsonResponse($json);

entities:

class Quest{
...
 /**

     * @OneToMany(targetEntity="AppBundle\Entity\Comment", mappedBy="quest")
     */
    protected $comments;
}



class Comments{
/**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
/**
     * @ORM\Column(type="integer")
     */
    protected $rating;
 /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Quest", inversedBy="comments")
     * @ORM\JoinColumn(name="quest_id", referencedColumnName="id")
     */
    protected $quest;
}

Answer 1

The easiest way (and also the best way for overall performances) is to store the rating of your Quest in the quest table, and refresh it each time a Comment is edited/added. You can use Doctrine Events for that.

This would solve both your questions, because this way you won't have to calculate at each request, and the rating will be automatically SELECTed when you'll request for the quest table.