mysql - cake php 如何做一个好的查询

Question

我有一个问题，我是 cakePHP 的菜鸟，我使用 cakePHP 1.3，我有一个包含很多子查询的查询，并尝试使用查找句子或类似的东西将其转换为 cake 的 queryBuilder:

我的查询是下一个:

 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.* #SubconsultaFROM    #   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` = 0GROUP BY  F.createdby) AS MensajeNuevo ON MensajeNuevo.usuario_id = PbFeedback.createdby WHERE    `PbFeedback`.`usuario_id` = 0GROUP BY    `PbFeedback`.`createdby`ORDER BY    `PbFeedback`.`createdby` ASCLIMIT 0, 30 ;

谢谢;)

Answer 1

我建议您研究一下ContainableBehavior:

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

Containable 允许您轻松构建复杂的查询，例如:

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