count with doctrine

I have two table:

Name
    id | name | city_id
    1  | aaa  | 1
    2  | vvv  | 2
    3  | ddd  | 2
    4  | sss  | 3
    5  | dds  | 1

etc

City:
id | name
1  | London
2  | NY
3  | Boston

etc

how can i get City and count:

name_city |开发者_开发技巧 count
London    | 2
NY        | 2
Boston    | 1

In City table:

$q = $this->createQuery('a')
           ->leftJoin('a.Name n')
           ->select('a.name_city as name_city, sum(n.city_id) as sum');

        return $q->execute();

but this is wrong.

You should use count() instead of sum(), and plus, you need a group by.

You do not appear to have a FROM clause, the object type is not specified for the a entity.

Also, read the aggregate values section in the documentation.

this post was kinda helpful but I though that I would add a little more details for anyone looking to join 2 tables and with an aggregate count.

e.g. this post (http://stackoverflow.com/questions/7837671/mysql-join-tables-and-count-instances) but in doctrine.

Using the example above the query would be (doctrine 2.0):

$q = $this->em->createQueryBuilder('a')
           ->select('a.name_city as name_city, count(n.city_id) as sum');
           ->from('city','a')
           ->leftJoin('a.Name n')
           ->groupBy('n.id')
           ->orderBy('sum')

    $query = $qb->getQuery();
    echo $qb->getDql();  // if you want to see the dql created
    $result = $query->getResult();