How to do multiple joins to the same table using joinAdd() in DB_DataObject
| From: | Andy Crain | Date: | Tue, 05 Aug 2003 23:11:05 +0000 |
| Subject: | How to do multiple joins to the same table using joinAdd() in DB_DataObject | ||
| Groups: | php.pear.general | ||
| Request: | Send a blank email to pear-general+get-7033@lists.php.net to get a copy of this message | ||
Is it possible, using joinAdd() and [database].links.ini, to construct a
query in which one table is joined twice to another table on the same
field, using aliases? Specifically, I'm trying to achieve something like
this:
SELECT
main_table.ownedby_emp_id,main_table.editedby_emp_id,owner_table.emp_nam
e,editor_table.emp_name
FROM main_table
INNER JOIN users AS owner_table ON
owner_table.emp_id=main_table.ownedby_emp_id
INNER JOIN users AS editor_table ON
editor_table.emp_id=main_table.editedby_emp_id
By using the following code...
//set up main_table object, then
require_once('DB/DataObject/Classes/Users.php');
$owned_by = new DataObjects_Users;
$edited_by = new DataObjects_Users;
$this->joinAdd($owned_by,'INNER','owner_table');
$this->joinAdd($edited_by,'INNER','editor_table');
$this->selectAs();
$this->selectAs($owned_by,'owned_by_%s','owner_table');
$this->selectAs($edited_by,'edited_by_%s','editor_table');
$this->find();
//etc.
...with my links.ini file containing the following:
[main_table]
ownedby_emp_id = users:emp_id
editedby_emp_id = users:emp_id
This results, however, in something like this (note the field names in
the joins; both joins join on the same field in the main table, rather
than the two different fields specified in links.ini):
SELECT
main_table.ownedby_emp_id,main_table.editedby_emp_id,owner_table.emp_nam
e,editor_table.emp_name
FROM main_table
INNER JOIN users AS owner_table ON
owner_table.emp_id=main_table.ownedby_emp_id
INNER JOIN users AS editor_table ON
editor_table.emp_id=main_table.ownedby_emp_id
Looking at DataObject::joinAdd(), it appears it will return only the
first link in links.ini in which the two objects' tablenames match, so
it never sees the second link relationship listed above. Is there some
way to construct a query like the one above without using getLink(),
getLinks() or query()--I'd rather not use getLink() or getLinks() so as
to accomplish in one query what these would do in two, and I'd rather
not use query() for portability reasons.
Thanks,
Andy