zend-framework zend-db zend-db-table relationships

Zend Framework relationships - defining column names in findManyToManyRowset()?

I'm working on an application developed using Zend Framework. I have defined relationships in models, and can use them happily, e.g:

$rowset = $row->findManyToManyRowset('People', 'Jobs');

However, i've hit a problem where the rowset is returned has column names that are the same in 'People' and 'Jobs', and therefore, merges the array keys, losing some of the data from the final rowset.

I understand I can pass a Zend_Db_Select object to findManyToManyRowset() as one of the parameters, but can't find any documentation explaining how to use it in this case, e.g.:

$select = $this->select()->from(array(
                                     'p' => 'people', 
                                     'j' => 'jobs'
                                     ),
                                array( 
                                     'person_id' => 'p.id',
                                     'job_id' => 'j.id',
                                     'person_code' => 'p.code',
                                     'job_code' => 'j.code'
                                     )
                                );

If i try to use the above code, I get a message such as:

Error: No reference rule "" from table People to table Jobs

Can anyone enlighten me on how this should be done? I know I could change my column names in the database, but i'd prefer a code change as opposed to re-designing my DB structure and updating all the related code.

Note: without some form of column aliasing as above, the rowset returned looks like this (ie., it merges the columns with the same names):

[_data:protected] => Array
    (
        [id] => 1
        [code] => SX342
    )

Cheers,
Matt

Solution

My first recommendation is that you shouldn't name columns such generic names like id and code. These names are meaningless, and as you have discovered they also result in collisions when you fetch results in an associative array.

You're also using the Select interface incorrectly. You should specify only one table per from() call or join() call.

Finally, I never try to do complex queries via the Zend_Db_Table relationships interface. It's intended only for simple cases. If you have a more complex query, just write the SQL query explicitly.