count with doctrine

Question

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.

score 3 · Accepted Answer · answered Sep 05 '11 at 15:11

3

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

answered Sep 05 '11 at 15:11

xdazz

158,678
38
247
274

Orbling · Answer 2 · 2011-09-05T15:12:44.037

1

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.

edited Sep 05 '11 at 15:12

answered Sep 05 '11 at 14:47

Orbling

20,413
3
53
64

score 1 · Answer 3 · answered Oct 24 '11 at 02:08

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();

count with doctrine

3 Answers3