CODEWITHSUNDEEP
symfonydoctrine-ormleft-joinquery-builder
Belkacem Yahiaoui
Belkacem Yahiaoui

Reputation: 61

left join queryBuilder symfony3

I'm using Doctrine's QueryBuilder to build a query, and I want to get the count of each value in the table alongside with the value.

this is the SQL query 

SELECT choice.choice ,count(*) 
FROM answer 
LEFT JOIN choice ON answer.answer_id_id = choice.id 
WHERE choice.survey_id=1  
GROUP BY choice ;

It returns a table with two columns, the value(choice) and the count

This is the query builder:

class AnswerRepository extends \Doctrine\ORM\EntityRepository
{
    public function getSurveyAnswers($idSurvey)
    {
        $query = $this->createQueryBuilder("a");
        $query
            ->select('COUNT(a.answerId),e.choice')
            ->leftJoin("a.answerId",'e')->addSelect('e.choice')
            ->where('e.Survey = :surv')
            ->setParameter('surv',$idSurvey);

        $kk = $query->getQuery()->getArrayResult();
        dump($kk);
        die();
    }
}

Now, when I run this it returns an array which contains the number of rows and the first value.

Upvotes: 0

Views: 1274

Answers (1)

Jakub Matczak
Jakub Matczak

Reputation: 15686

You have no groupby() in your query builder, therefore SQL database returns all rows agregated into a single one.

Try to add grouping to your query builder:

->groupby('e.choice')

Upvotes: 1

Related Questions