Zend_DB_Select : why are all fields returned?

Question

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.*?

karim79 · Accepted Answer · 2009-08-16 08:21:07Z

3

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.

edited Aug 16, 2009 at 8:21

answered Aug 16, 2009 at 8:03

karim79

343k67 gold badges420 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codecowboy Over a year ago

Thanks. the manual also says joinInner(table, join, [columns]) methods. Don't the square brackets mean that is optional?

karim79 Over a year ago

@codecowboy - yes it does, I've added the method signature to my answer.

Collectives™ on Stack Overflow

Zend_DB_Select : why are all fields returned?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related