How to use case in this case - CakePhp 3

Question

I have to upgrade the version of cakephp ( x2 to x3 ), I have to translate this horrible sql expression. I started to translate but I am blocked each time.

    public function getDefautPPKLAC($nuit, $date_deb, $date_fin) {

        return $this->query("SELECT d.cycle_id, d.code_element, d.date_debut, d.date_fin, (d.date_fin-d.date_debut) AS duree, 


CASE WHEN d.code_defaut='PP_PPKLAC_T' THEN d.code_defaut ELSE 'PP_PPCLAC_T'END AS code_defaut, 
CASE WHEN d.code_defaut<>'PP_PPKLAC_T' THEN d.code_defaut END AS origine, 
CASE WHEN d.code_defaut<>'PP_PPKLAC_T' THEN (SELECT libelle FROM referentiel_traces WHERE code=d.code_defaut) END AS info

                            FROM vue_defauts as d
                            WHERE d.code_defaut IN ('DETEC_BDU', 'DETEC_DILH', 'DETEC_DILB','DISC_N1_BDU','DISC_N2_BDU','DISC_PIZZ_BDU','DISC_DILH','DISC_DILB','DISC_PPF','DISC_K123','DISC_K507','DIL_INHIB','PP_PPKLAC_T') 
                            AND d.nuit = '" . $nuit . "'
                            AND d.date_debut BETWEEN '" . $date_deb . " 00:00:00' AND '" . $date_fin . " 23:59:59.999'
                            ORDER BY d.cycle_id, d.date_debut, d.code_element, d.code_defaut desc");
    }

For now I tried to do this, but I am pretty sure this is totally wrong. (not finished)

        $this->loadModel('ReferentielTraces');

        $query = $this->find();
        $code_defaut = $query->newExpr()->addCase(
            $query->newExpr->add(
                ['code_defaut'=>'PP_PPKLC_T'],
                ['code_defaut'=>'identifier','PP_PPCLAC_T'],
                ['string','string']
            )
        );

        $origine = $query->newExpr()->addCase(
            $query->newExpr->add(
                ['code_defaut <>'=>'PP_PPKLAC_T'],
                ['code_defaut'=>'identifier'],
                ['string']
            )
        );
        $info = $query->newExpr()->addCase(
            $query->newExpr->add(
                ['code_defaut <>'=>'PP_PPKLAC_T'],
                [$this->ReferentielTraces->select(['libelle'])->where(['code'=>'code_defaut'])->first()] // wrong here
                ['string']
            )
        );
        $listCodeDefaut = array('DETEC_BDU', 'DETEC_DILH', 'DETEC_DILB','DISC_N1_BDU','DISC_N2_BDU','DISC_PIZZ_BDU','DISC_DILH','DISC_DILB','DISC_PPF','DISC_K123','DISC_K507','DIL_INHIB','PP_PPKLAC_T');
        $query->where(['code_defaut IN'=>$listCodeDefaut,'nuit'=>$nuit]); //between ?
        $query->order(['cycle_id','date_debut','code_element','code_defaut'=>'DESC']);

Can you help me please?

score 1 · Accepted Answer · answered Dec 03 '19 at 16:24

Better use it as it is.

Use connection manager :

use Cake\Datasource\ConnectionManager;

connection is defined in config

$conn = ConnectionManager::get('default_connection');

run sql:

$stmt = $conn->execute($sql);

Executing & Fetching Rows
After preparing a statement and binding data to it, you can execute it and fetch rows. Statements should be executed using the execute() method. Once executed, results can be fetched using fetch(), fetchAll() or iterating the statement:

// Read one row.
$row = $stmt->fetch('assoc');

// Read all rows.
$rows = $stmt->fetchAll('assoc');

// Read rows through iteration.
foreach ($stmt as $row) {
    // Do work
}

Here is a link that might help you : execute query in cakephp

How to use case in this case - CakePhp 3

1 Answers1