Reputation: 10124
public function getWorksheetData($id) {
/** create the following query using select object:
SELECT wc.label, wd.notes FROM worksheet_data wd
LEFT JOIN worksheet_columns wc ON wd.column_id = wc.id;
*/
$id = (int) $id;
$select = $this->_db->select()
->from(array('wd'=>'worksheet_data'),
array('wc.label','wd.notes'))
->join(array('wc'=>'worksheet_columns','wd.column_id = wc.id'))
->where("wd.id = :worksheet_id");
$results = $this->_db->fetchAll($select, array('worksheet_id' => $id),Zend_Db::FETCH_ASSOC);
return array('results'=>$results);
}
Why does this query become:
SELECT wc
.label
, wd
.notes
, wc
.* FROM worksheet_data
AS wd
INNER JOIN worksheet_columns
AS wc
WHERE (wd.id = :worksheet_id)
and return wc.*?
Upvotes: 1
Views: 427
Reputation: 342795
You need to put an empty array as the third argument to the join method, also the join condition should not be part of the first argument's array, but as the second argument instead (you'll notice that there is not join condition in your query). Join needs at least the first two arguments, this is the method signature:
join(table, join, [columns])
Applying that to your join method:
->join(array('wc'=>'worksheet_columns'),'wd.column_id = wc.id', array())
so:
$select = $this->_db->select()
->from(array('wd'=>'worksheet_data'),
array('wc.label','wd.notes'))
->join(array('wc'=>'worksheet_columns'),'wd.column_id = wc.id', array())
->where("wd.id = :worksheet_id");
gives the output:
SELECT `wc`.`label`, `wd`.`notes` FROM `worksheet_data` AS `wd` INNER JOIN `worksheet_columns` AS `wc` ON wd.column_id = wc.id WHERE (wd.id = :worksheet_id)
The manual says:
To select no columns from a table, use an empty array for the list of columns.
Upvotes: 3