Re: How to do multiple joins to the same table using joinAdd() in DB_DataObject

joinAdd is basically very problematic.. - JOINS syntax for databases is not very standard. however what you have may work... have a play with the joinAdd code, and see if you can fix it.. - I'll patch it up here. In general though, I'm very tempted to do emulated joins in the future, using IN (a,b,c,d,e) and multiple queries, as it will probably be more portable, and alot simpler to understand.. (and from previous experience may even be faster..) - basically alot of the time JOIN's tend to end up with very unpredictable results. Regards Alan Andy Crain wrote:

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