JoinLeft Zend Framework, same field names in different tables

Question

I have a problem. I am trying to leave two tables with Zend Framework using the $ select object. Unfortunately, my tables have a common "name" field, and when I join one with the other, the result I get is that the name field from the table overwrites the name field from the other.

My code looks something like this:

$select->joinLeft ( array ('users' => 'users' ), $this->_name . '.employee_id = users.user_id', array ('*' ) ); 

How can I join tables and avoid this problem?

Answer 1

Use table aliases, as in any regular sql query!

With the aliases Zend_Db it is written like this:

 $select = $db->select() ->from(array('p' => 'products'), array('product_id', 'product_name')) ->join(array('l' => 'line_items'), 'p.product_id = l.product_id', array() ); // empty list of columns 

A non-zend request would look like this:

 SELECT p.product_id, p.product_name FROM products AS p JOIN line_items AS l ON p.product_id = l.product_id; 

Answer 2

I guess it's a little late, but to get all fields from two tables, you must alias all fields

 $select = $db->select() ->from(array('u' => 'users'), array('u.id'=>'u.id','u.employee_id'=>'u.employee_id','u.name'=>'u.name')) ->joinLeft(array('e' => 'employees'), 'e.id = u.employee_id', array('e.id'=>'e.id','e.name'=>'e.name') ); 

And your array will look like this:

 array( 0=>array( 'u.id'=>'1', 'u.employee_id'=>'1', 'u.name'=>'John Doe', 'e.id'=>'1', 'e.name'=>'Worker' ), 1=>array( ... ));