Subquery in CakePhp with InnerJoin

Question

I am trying to convert this query:

SELECT
    pdd.pedinte_id, pdd.data, pdd.situacao as Situacao, pdd.valor_total, pdd.qtd_etiquetas, 
    (
        SELECT count(pdi.envio_id)
        FROM pedinte_item pdi
        INNER JOIN envios env ON
            pdi.envio_id = env.envio_id
        WHERE
            pdi.pedinte_id = pdd.pedinte_id AND
            env.Situacao = 2
    ) AS TemErros
FROM pedinte pdd
left join user usr on
    usr.user_id = pdd.user_id
WHERE pdd.user_id IS NOT NULL AND pdd.pedinte_id IS NOT NULL;

to CakePhp:

removed code, maybe very wrong.

Without success.

I have 4 tables:

• pedinte (pdd)
• pedinte_item (pdi)
• envios (env)
• user (usr)

pedinte > pedinte_item > envios (count)

Cant believe, harder to do the query builder than the mysql code.

Answer 1

(This does not address the CakePHP question, but too long for a Comment)

(Subqueries are not always "bad".)

These indexes may help performance:

usr:  INDEX(user_id)
pdi:  INDEX(pedinte_id, envio_id)
env:  INDEX(Situacao, envio_id)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.