CODEWITHSUNDEEP
zend-frameworkselectzend-db
codecowboy
codecowboy

Reputation: 10124

Zend_DB_Select : why are all fields returned?

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

Answers (1)

karim79
karim79

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

Related Questions