rename fields in Zend select/join query

Question

I have 2 Tables:

region: region_id,name,state_id
state: state_id,name

I want both names in my result, state.name renamed to statename.
So far I got this:

$select = $select->from(array('r' => 'region'))->join(array('s' => 'state'),
'r.state_id = s.state_id',array("statename" =>"r.name"));

which results in following query:

SELECT `r`.*, `r`.`name` AS `statename` FROM `region` AS `r`
INNER JOIN `state` AS `s` ON r.state_id = s.state_id

So i just need to change r.name AS statename to s.name AS statename.
But i cant get it to work. If i change the last part of the select to array("statename" =>"s.name"), i get an error

Select query cannot join with another table

So how can i rename a field in the joining table?

Answer 1

You have to remove the integrity check.

    $table = new self();
    $select = $table->select()->setIntegrityCheck(false);
    $select = $select->from(array('r' => 'region'))->join(array('s' => 'state'),'r.state_id = s.state_id',array("statename" =>"s.name"));

The integrity check is there to make sure your query is not going to use columns from another table, so Zend_Db_Table_Row objects can be updated, saved or deleted. If you remove the integrity, you're telling Zend that you know what you're doing and you want to use columns from another table.

Here's a brief explanation from documentation:

The Zend_Db_Table_Select is primarily used to constrain and validate so that it may enforce the criteria for a legal SELECT query. However there may be certain cases where you require the flexibility of the Zend_Db_Table_Row component and do not require a writable or deletable row. for this specific user case, it is possible to retrieve a row or rowset by passing a FALSE value to setIntegrityCheck(). The resulting row or rowset will be returned as a ‘locked’ row (meaning the save(), delete() and any field-setting methods will throw an exception).