Doctrine Query Builder add a count column of a related table-CodePudding

I want to show a count of a related table to the one building the query builder. The working native query is the following one:

select id, name, (select count(*) from houses where status = 1 and  street_id = s.id) as total from streets s;

In the former query I want to show the street info and also how many houses there are in each street.

I started the query builder like this

$queryBuilder = $this->createQueryBuilder('s');
    $queryBuilder
        ->select('
            s.id as street_id, s.name as name')

But I don't know how to add this count. On another hand I tested this query natively with joins and it is very slow, so I would need to avoid them.

House and Street are both ORM Entities

CodePudding user response：

If native Sql is slow then ORM will be not faster. first question is why query is slow, if there is many data, try to load count of houses via ajax async, or maybe you can limit you data which you can fetch from db.

CodePudding user response：

To answer at how you could add the "count" to the query builder, you should be able to do something like this

$queryBuilder
   ->select('s.name, s.id, count(h)')
   ->innerJoin(HouseEntity::class, 'h', 'WITH', 's.id = h.street_id)
   ->where('h.status = 1)
   ->groupBy('s.name, s.id');

You mentioned avoiding JOIN, you maybe able to avoid the whole query by adding a collection of house in the street entity and using ->getHouses(), but I guess Doctrine would still be doing a SQL join under the hood