Zend Framework relationship - defining column names in findManyToManyRowset ()?
I am working on an application developed using Zend Framework. I have defined relationships in models and can use them happily, for example:
$rowset = $row->findManyToManyRowset('People', 'Jobs');
However, I ran into a problem where the rowset is returned, the column names are the same in People and Jobs, and so merging the array keys results in some data being lost from the final rowset.
I understand that I can pass an object Zend_Db_Select
to findManyToManyRowset()
as one of the parameters, but cannot find documentation explaining how to use it in this case, for example:
$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 like:
Error: No reference rule "" from table People to table Jobs
Can anyone enlighten me on how to do this? I know I can change the column names in the database, but I would rather change the code than redesign my DB structure and update all associated code.
Note: without any form of column aliases as above, the rowset returned looks like this (for example, it concatenates columns with the same name):
[_data:protected] => Array
(
[id] => 1
[code] => SX342
)
Cheers,
Matt
source to share
My first recommendation is that you should not specify columns with common names like id
and code
. These names don't make sense, and as you discovered, they also lead to collisions when retrieving results in an associative array.
You are also using the Select interface incorrectly. You only have to specify one table to call from()
or join()
.
Finally, I never try to run complex queries through the links interface Zend_Db_Table
. It is only intended for simple cases. If you have a more complex query, just write the SQL query explicitly.
See also How do I execute a concatenated query in the ZF table interface?
source to share
I know this answer comes a little later, but here are some things to point out.
1) findManyToManyRowset($matchTable, $intersectionTable, $callerRefRule, $matchRefRule, $select);
- if you are transmitting Zend_Db_Table_Select
, you need to transmit null
for rules.
2) Zend_Db_Table_Select
passed to findManyToManyRowset()
must be created from $matchTable
, and it is safe to assume that the where clauses i
have an alias for the intersection table, and m
is an alias for the lookup table.
3) In case of collisions, m
get the name of the key in the associative array returned by php. The completed request 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 findManyToManyRowset()
will be a Rowset created from $matchTable
, so if you need to grab any information from the intersecting table as well as grab the data for the lookup table, you will probably need to create a custom one Zend_Db_Select
and not use the data Zend_Db_Table
mapping stuff anyway.
So here's a working example using People as the lookup table Workers as the intersection table and lets say Customers as the source table. Assuming in this example that tables are linked by 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'));
source to share