yii 多个内连接 [英] yii multiple inner joins

问题描述

我正在尝试在 yii 中编写查询.我有以下工作

I am trying to write a query in yii. I have the following which works

$criteria = new CDbCriteria;
$criteria->condition = "($column = :id)";
$criteria->params = array(":id" =>  $id );
$rows = Jobs::model()->with('pROJ')->findAll($criteria);

这将返回数组中的 Jobs 模型.我需要在 yii 中编写以下查询以返回模型

This returns the model of Jobs in array. I need to write the following query in yii to return a model

SELECT jobs.JOBNO, jobs.STATUS, projects.ORDERNO, jobs.PROJID, jobs.NAME, jobs.SEQ, jobs.PCENTDONE, jobs.EARNED, jobs.VALUE, jobs.DATEIN, jobs.DATEDONE, jobs.DATEDUE, jobs.SENTBACK, jobs.ORIGTAPES, jobs.COMMENTS, projects.CATEGORY, orders.BIDNO
FROM (jobs INNER JOIN projects ON jobs.PROJID = projects.PROJID) INNER JOIN orders ON projects.ORDERNO = orders.ORDERNO
where jobs.projid =     3002001
ORDER BY jobs.JOBNO, jobs.PROJID 

我尝试了以下方法但没有用

I have tried the following but it does not work

$rows = Yii::app()->db->createCommand()
            ->select('jobs.*, projects.ORDERNO, projects.CATEGORY, orders.BIDNO')
            ->from('jobs, projects, orders')
            ->join('projects p','jobs.PROJID = p.PROJID')
            ->join('orders o', 'p.ORDERNO = o.ORDERNO')
            ->where('jobs.projid=:id', array(':id'=>$id))
            ->queryRow(); 

我收到以下错误

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'jobs.PROJID' in 'on clause'. The SQL statement executed was: SELECT `jobs`.*, `projects`.`ORDERNO`, `projects`.`CATEGORY`, `orders`.`BIDNO`
FROM `jobs`, `projects`, `orders`
JOIN `projects` `p` ON jobs.PROJID=p.PROJID
JOIN `orders` `o` ON p.ORDERNO=o.ORDERNO
WHERE jobs.projid=:id 

我已经更新到

$rows = Yii::app()->db->createCommand()
                ->select('jobs.*, projects.orderno, projects.category, orders.bidno')
                ->from('jobs')
                ->join('projects p','jobs.projid = p.projid')
                ->join('orders o', 'p.orderno = o.orderno')
                ->where('jobs.projid=:id', array(':id'=>$id))
                ->queryRow();  

但我仍然收到错误消息.mysql中的所有列都是大写的

but i still get the error. All columns in mysql are CAPS

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects.orderno' in 'field list'. The SQL statement executed was: SELECT `jobs`.*, `projects`.`orderno`, `projects`.`category`, `orders`.`bidno`
FROM `jobs`
JOIN `projects` `p` ON jobs.projid = p.projid
JOIN `orders` `o` ON p.orderno = o.orderno
WHERE jobs.projid=:id 

推荐答案

正如@DCoder 所说:您的选择现在应该阅读 select('jobs.*, p.orderno, p.category, o.bidno').为了保持一致性，您还应该将 jobs 别名如下

As @DCoder said: your select should now read select('jobs.*, p.orderno, p.category, o.bidno'). For consistency you should also alias jobs as follows

$rows = Yii::app()->db->createCommand()
            ->select('j.*, p.orderno, p.category, o.bidno')
            ->from('jobs j')
            ->join('projects p','j.projid = p.projid')
            ->join('orders o', 'p.orderno = o.orderno')
            ->where('j.projid=:id', array(':id'=>$id))
            ->order('j.jobno,j.projid')
            ->queryRow();

这篇关于yii 多个内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！