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!

Matthew Ratzloff · Accepted Answer · 2011-05-14 02:47:11Z

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, 2011 at 2:47

Matthew Ratzloff

4,6631 gold badge34 silver badges37 bronze badges

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

1 Comment

gaRex Over a year ago

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?"

Brian Fisher · Accepted Answer · 2011-05-14 02:54:04Z

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, 2011 at 2:54

answered May 14, 2011 at 2:44

Brian Fisher

24.1k15 gold badges81 silver badges82 bronze badges

4 Comments

dextervip Over a year ago

@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

Brian Fisher Over a year ago

For debugging purposes can you hardcode the $info['name'] to 'beneficios' and the $id_projeto to 8 and let us know what happens.

dextervip Over a year ago

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"

gaRex Over a year ago

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

Collectives™ on Stack Overflow

SQL to Zend_Db_Table

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related