StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POyii multiple inner joins
text
Body
copied!<p>I am trying to write a query in yii. I have the following which works</p> <pre><code>$criteria = new CDbCriteria; $criteria->condition = "($column = :id)"; $criteria->params = array(":id" => $id ); $rows = Jobs::model()->with('pROJ')->findAll($criteria); </code></pre> <p>This returns the model of Jobs in array. I need to write the following query in yii to return a model</p> <pre><code>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 </code></pre> <p>I have tried the following but it does not work</p> <pre><code>$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(); </code></pre> <p>I get the following error</p> <pre><code>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 </code></pre> <p>I have updated to </p> <pre><code>$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(); </code></pre> <p>but i still get the error. All columns in mysql are CAPS</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload