SQL to Zend_Db_Table

Question

I'm trying to convert a SQL to Zend_Db_Table

SELECT c1.* FROM beneficios c1
left join beneficios c2 on c1.document_id = c2.document_id and c1.versao <       c2.versao
where c1.id_projeto = 8 and c2.document_id is null order by ordem ASC;

I have a method inside a zend db table class

$info = $this->info();
    $select = $this->select()
         ->from(array('c1' => $info['name']))
         ->joinLeft(array('c2' => $info['name']),
                'c1.document_id = c2.document_id and c1.versao < c2.versao')
            ->where('c2.document_id is null')
            ->where('c1.id_projeto = ?', $id_projeto)
            ->order('ordem ASC');

    return $this->fetchAll($select);

I get the following error

Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ordem' in order clause is ambiguous

if I remove order

Zend_Db_Statement_Exception: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

What's the correct way to convert that SQL?

If anyone could help me, Thanks!

Answer 1

Instead of $this->select() use $this->getAdapter()->select(). Also you can specify that you don't want any of the columns from table c2 by passing an empty array to the joinLeft function:

$info = $this->info();
    $select = $this->getAdapter->select()
         ->from(array('c1' => $info['name']))
         ->joinLeft(array('c2' => $info['name']),
                'c1.document_id = c2.document_id and c1.versao < c2.versao', array())
            ->where('c2.document_id is null')
            ->where('c1.id_projeto = ?', $id_projeto)
            ->order('ordem ASC');

return $this->fetchAll($select);