how to count elements after making a left join with doctrine

Question

i have 2 tables: "User" and "States". What i pretend to do, is to count how many users are from certain state, ie:

state total users santa fe 5 buenos aires 20

and so on.

I'm using codeigniter with doctrine, here's my code:

public function countByState(){
    $this->qb = $this->em->createQueryBuilder();

    $this->qb->select('s.state_id', $this->qb->expr()->count('u.state'))
             ->from('models\States', 's')
             ->leftJoin('s.state_id' , 'u')
             ->leftJoin('models\User', 'u')
             ->groupBy('s.state_id');

    $query = $this->qb->getQuery();
    $obj = $query->getResult();

    return $obj;        
}

and this is the error: Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Semantical Error] line 0, col 76 near 'u LEFT JOIN models\User': Error: Class models\States has no association named state_id' in C:\Desarrollo\new_frame_doctrine\site\application\libraries\data\Doctrine\ORM\Query\QueryException.php on line 47

@AmitKumar thanx amit, i know how to do it in sql, but not in dql, so that was the question — Limon

Amit Amit · Accepted Answer · 2013-09-12T17:29:42

Please use this sql query

SELECT s.name,count(*) FROM `users` u left join `state` s on u.stateid=s.id 
 group by s.id;

In the code please change accordingly!!

I tried this by creating two tables named state (with fields id and name) and users(with fields id, name and state_id), It worked fine for me.

Cheers!!!

how to count elements after making a left join with doctrine

1 Answers