cake php how do a good query

Question

I have a problem, i am noob with cakePHP and i use cakePHP 1.3 and i have a query with a lot subquerys and try to convert this to queryBuilder of cake using find sentences or things like this:

My query is the next-one:

 SELECT
    `PbFeedback`.`id`,
    `PbFeedback`.`createdby`,
    `PbFeedback`.`created`,
    `PbFeedback`.`msg`,
    `Usuario`.`name`,
    `PbFeedback`.`tipo`,
    COUNT(IF(PbFeedback.msg = 0, 1, 0))AS totalMsg,
    `Entidad`.`nombre`
    ,UltimoMensaje.* , #Subconsulta
    MensajeNuevo.* #Subconsulta
FROM
    #   A                   A
    `eon_feedback` AS `PbFeedback` 
    LEFT JOIN 
    #   B                     B
    `eon_sys_usuarios` AS `Usuario` ON(`PbFeedback`.`createdby` = `Usuario`.`id`)
LEFT JOIN `eon_entidades` AS `Entidad` ON(
    `PbFeedback`.`entidad_id` = `Entidad`.`id`
)
LEFT JOIN (SELECT
    `F`.`createdby`,
    `F`.`created`,
    `F`.`msg`,
    `F`.`tipo`
FROM eon_feedback AS `F` 
 GROUP BY `F`.`createdby`  
ORDER BY F.created DESC  
 ) AS UltimoMensaje ON (UltimoMensaje.createdby = `PbFeedback`.`createdby`)
LEFT JOIN ( 
    SELECT
    F.createdby AS usuario_id,
    COUNT(*) AS `count`
FROM
    `eon_feedback` AS `F`
WHERE `F`.`status` = 0
GROUP BY  F.createdby
) AS MensajeNuevo ON MensajeNuevo.usuario_id = PbFeedback.createdby 
WHERE
    `PbFeedback`.`usuario_id` = 0
GROUP BY
    `PbFeedback`.`createdby`
ORDER BY
    `PbFeedback`.`createdby` ASC

LIMIT 0,
 30 ;

Thanks ;)

Olivier Olivier · Accepted Answer · 2015-04-07T16:59:13

I would suggest you look into the ContainableBehavior:

http://book.cakephp.org/1.3/en/The-Manual/Core-Behaviors/Containable.html

Containable allows you to easily build complex queries, for example:

$this->PbFeedback->find('all', array(
    'contain' => array (
        'Usuario',
        'UltimoMensaje ',
        'eon_feedback' => array (
            'fields' => array ('created','msg','tipo'),
            'conditions' => array ('eon_feedback.status =' => '0')
        )
    ),
    'limit' => 30
);

cake php how do a good query

1 Answers