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!

score 1 · Answer 1 · answered May 14 '11 at 02:47

1

It's just as it says: "Column 'ordem' in order clause is ambiguous". Prefix ordem with either c1. or c2., depending on which table's ordem column you want to sort by.

answered May 14 '11 at 02:47

Matthew Ratzloff

4,518
1
31
35

This is a correct answer! Just try goo on "Column in order clause is ambiguous". Ambiguous is means "Hey, you have in all of your tables two columns with same name! Which one did you means realy?" – gaRex May 14 '11 at 11:58

Brian Fisher · Accepted Answer · 2011-05-14T02:54:04.410

0

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);

edited May 14 '11 at 02:54

answered May 14 '11 at 02:44

Brian Fisher

23,519
15
78
82

@Brian Fisher Hi, it returns me Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 – dextervip May 14 '11 at 02:56
For debugging purposes can you hardcode the $info['name'] to 'beneficios' and the $id_projeto to 8 and let us know what happens. – Brian Fisher May 14 '11 at 02:59
I replaced it but still returns me the same error, I printed the SQL: "SELECT `c1`.* FROM `beneficios` AS `c1` LEFT JOIN `beneficios` AS `c2` ON c1.document_id = c2.document_id and c1.versao < c2.versao WHERE (c2.document_id is null) AND (c1.id_projeto = 8) ORDER BY `ordem` ASC" – dextervip May 14 '11 at 03:08
It shouldn't work, as column "ordem" is in both aliases tables (which is one table). So he just needs to put alias name in order. See anwser of @Matthew – gaRex May 14 '11 at 11:54

SQL to Zend_Db_Table

2 Answers2