如何在cakephp中实现“有案例时”

Question

Cake php code -

$this->paginate = array(
    'Lead' => array(
        'joins'=>array(
                array(
                    'type'=>'left',
                    'table'=>'lead_countries',
                    'alias'=>'LeadCountry',
                    'conditions'=>array(
                        'LeadCountry.lead_id = Lead.id',
                    ),

                )
            ),
        'conditions' => $conditions,
        'order' => array('LeadCountry.lead_id' => 'DESC'),
        'group' => array('LeadCountry.lead_id'), //fields to GROUP BY               
        'limit' => $per_page
    )
);
$leads = $this->Paginator->paginate('Lead');
$this->set(compact('leads'));

It's mysql query

SELECT `Lead`.`id` , `LeadCountry`.`primary_email` , `LeadCountry`.`first_name` , `LeadCountry`.`lead_id` , `LeadCountry`.`id`
FROM `leads` AS `Lead`
INNER JOIN `ekowarehouse`.`lead_countries` AS `LeadCountry` ON ( `LeadCountry`.`lead_id` = `Lead`.`id` )
GROUP BY `Lead`.`id`
HAVING sum(
CASE WHEN `LeadCountry`.`active` <>0
THEN 1
ELSE 0
END ) =0

how to implement below this section in above cake php code

HAVING sum(
CASE WHEN `LeadCountry`.`active` <>0
THEN 1
ELSE 0
END ) =0

in cake php

Answer 1

Implement “having sum case when” in cakephp

$this->paginate = array(
    'Lead' => array(
        'joins'=>array(
                array(
                    'type'=>'left',
                    'table'=>'lead_countries',
                    'alias'=>'LeadCountry',
                    'conditions'=>array(
                        'LeadCountry.lead_id = Lead.id',
                    ),

                )
            ),
        'conditions' => $conditions,
        'order' => array('LeadCountry.lead_id' => 'DESC'),
        //'group' => array('LeadCountry.LeadCountry'), //fields to GROUP BY
        'group' => '`LeadCountry`.`LeadCountry` HAVING sum(case when `LeadCountry`.`active` <> 0 then 1 else 0 end) = 0', //fields to GROUP BY              
        'limit' => $per_page
    )
);
$leads = $this->Paginator->paginate('Lead');
$this->set(compact('leads'));