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

Question

Hi,
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

Answer 1

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.

See also http://stackoverflow.com/questions/1412799/how-to-do-a-joined-query-in-the-zf-tables-interface/1423381#1423381

Answer 2

I know this answer comes a little late but here are some things to point out.

1) findManyToManyRowset($matchTable, $intersectionTable, $callerRefRule, $matchRefRule, $select); -- if you are passing a Zend_Db_Table_Select you are going to want to pass null for the rules.

2) The Zend_Db_Table_Select passed into the findManyToManyRowset() should be created from $matchTable and it is safe to assume that in the where clauses i is the alias for the intersection table, and m is the alias for the match table.

3) In the case of collisions, m will win the key name in the associative array returned in php. The query executed looks like this:

  SELECT 
    `i`.*, `m`.* 
  FROM 
    `interscetTable` AS `i` 
  INNER JOIN 
    `matchTable` AS `m` 
  ON
    `i`.`fk_m` = `m`.`pk` WHERE (`i`.`fk_o` = ?)  

4) No matter what, The return value of findManyToManyRowset() will be a Rowset created from the $matchTable so, if you need to capture any information from the intersecting table, while also capturing the data for the match table, you will probably need to have a custom Zend_Db_Select and avoid using the Zend_Db_Table stuff to map the data anyway.

So a working example, using "People" as the match table, "Workers" as the intersection table and lets say "Clients" as the originating table.. Assuming for this example that the tables link together something like: People.id:... -> workers.person_id:client_id:job_id -> clients:id:...

$client = $clientTable->fetchRow(); /// grab a random client

// fetch all people that have worked for the client ordered by their last name.
$client->findManyToManyRowset("People", "Workers", null, null, 
  $peopleTable->select()->order('m.lastname')); 

// fetch all people that have worked for the client ordered by their hire date:
// `workers`.`hiredate`
$client->findManyToManyRowset("People", "Workers", null, null, 
  $peopleTable->select()->order('i.hiredate'));