Question 42012 in PHP Question posted in
Official manual can be found here.

Doctrine Query Builder add a count column of a related table – PHP

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    To answer how you could add the count to the query builder:

    $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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search