php - 如何将 PDO 查询转换为 Yii 查询？

Question

有人可以帮我将以下代码转换为 Yii 查询吗？我希望它返回带有派生列的类型模型数组

$sql = 'UPDATE jobs
    RIGHT JOIN (
        SELECT jobs.JOBNO,
            round(details' . $type['type'] . '.' . $type['km'] . ' * sum(PRICE),2) AS JOBSVALUE
        FROM jobs
        JOIN projects ON jobs.PROJID = projects.PROJID
        JOIN biditems ON projects.id = biditems.project_id
        JOIN details' . $type['type'] . ' on jobs.JOBNO = details' . $type['type'] . '.JOBNO
        WHERE jobs.PROJID = :pid
        GROUP BY jobs.JOBNO
    ) AS temp ON jobs.JOBNO = temp.JOBNO
    SET jobs.VALUE = JOBSVALUE';

$command=$connection->createCommand($sql);
$command->bindValue(":pid", $model->PROJID,PDO::PARAM_INT);
$command->execute();


$sql = "UPDATE jobs j
    JOIN (
        SELECT j.JOBNO, COUNT(l.JOBNO) AS numlis
        FROM lineitems l
        RIGHT JOIN jobs j ON j.JOBNO = l.JOBNO
        WHERE j.PROJID = :pid
        GROUP BY j.JOBNO
    ) t ON j.JOBNO = t.JOBNO
    SET `VALUE` = 0, `EARNED` = 0
    WHERE PROJID = :pid AND t.numlis = 0;";

$command=$connection->createCommand($sql);
$command->bindValue(":pid", $model->PROJID,PDO::PARAM_INT);
$command->execute();

第一次尝试

$sql = "select jobs.JOBNO, round(details".$type['type'].".".$type['km']." * sum(PRICE),2) AS JOBSVALUE
                from jobs
                join projects on jobs.PROJID = projects.PROJID
                join biditems on projects.id = biditems.project_id
                join details".$type['type']." on jobs.JOBNO = details".$type['type'].".JOBNO
                where jobs.PROJID = :pid
                GROUP BY jobs.JOBNO";                       
        $command=$connection->createCommand($sql);
        $command->bindValue(":pid",$model->PROJID,PDO::PARAM_INT);
        $result = $command->queryAll();
        foreach ($result as $value) {
            $job = Jobs::model()->findByPk($value['JOBNO']);
            $job->VALUE = $value['JOBSVALUE'];
            $job->save();
        }

$sql = "SELECT j.JOBNO, COUNT(l.JOBNO) AS numlis
                    FROM lineitems l
                    RIGHT JOIN jobs j ON j.JOBNO = l.JOBNO
                    WHERE j.PROJID = :pid
                    GROUP BY j.JOBNO";
        $command=$connection->createCommand($sql);
        $command->bindValue(":pid",$model->PROJID,PDO::PARAM_INT);
        $result = $command->queryAll();
        foreach ($result as $value) {
            if($value['numlis'] == 0){
                $job = Jobs::model()->findByPk($value['JOBNO']);            
                $job->VALUE = 0;
                $job->EARNED = 0;           
                $job->save();
            }
        }

Answer 1

只需严格遵循他们的文档即可:

$job = Yii:app()->db
    ->createCommand()
    ->select(
                'jobs.JOBNO, round(details'.$type['type'].'.'.$type['km'].' * sum(PRICE), 2)'
            )
    ->join('projects', 'jobs.PROJID = projects.PROJID')
    ->join('biditems', 'projects.id = biditems.project_id')
    ->join('details'.$type['type'], 'jobs.JOBNO = details'.$type['type'].'.JOBNO')
    ->where('jobs.PROJID=:pid', array(':pid' = $model->PROJID))
    ->group('jobs.JOBNO')
    ->queryRow();