[FIXED] CakePHP query with CASE WHEN THEN ELSE condition

Issue

SELECT CASE WHEN a.type = 'property' THEN p.name ELSE r.name END AS `name`, a.*
FROM admin_alert a
LEFT JOIN  property p ON a.related_id = p.id 
LEFT JOIN  publisher r ON a.related_id = r.id 

How to write this sql in CakePHP?

Solution

$AdminAlerts = TableRegistry::get('AdminAlert')->find();

    $AdminAlerts = $AdminAlerts->join([
        'table' => 'property',
        'alias' => 'pr',
        'type' => 'LEFT',
        'conditions' => 'related_id = pr.id AND type = "property"',
    ]);
    $AdminAlerts = $AdminAlerts->join([
        'table' => 'publisher',
        'alias' => 'pu',
        'type' => 'LEFT',
        'conditions' => 'related_id = pu.id AND type = "publisher"',
    ]);

    $relatedName = $AdminAlerts->newExpr()
            ->addCase(
                [$AdminAlerts->newExpr()->add(['type' => 'property']),
                $AdminAlerts->newExpr()->add(['type' => 'publisher'])],

                [ new IdentifierExpression('pr.name'), new IdentifierExpression('pu.name')]

            );
    $AdminAlerts = $AdminAlerts->select(['id','related_id','type','title','message','name' => $relatedName]);

This worked

Answered By - suneeth shihab