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

Alan, Here's a quick hack to joinAdd that resolves the problem for me. It's pretty clumsy, and I'm sure there must be a better way, but it works for me. New code is marked. The only way I could figure to do this was to add an additional optional parameter, $joinCol, which is the column targeted for the join. Also, targeting this way only works on fields in the "join to" side of links.ini entries, i.e. the right side, as in table:field. In other words, this will work: $sourcebook->joinAdd($owned_by,'INNER','emp_id','owner'); $sourcebook->joinAdd($edited_by,'INNER','editby_emp_id','editor'); [src_main] emp_id = users:emp_id editby_emp_id = users:emp_id while this will not (since $links[users] will have only one element for emp_id): $sourcebook->joinAdd($owned_by,'INNER','emp_id','owner'); $sourcebook->joinAdd($edited_by,'INNER','editby_emp_id','editor'); [users] emp_id = src_main:editby_emp_id emp_id = src_main:emp_id /** * joinAdd - adds another dataobject to this, building a joined query. * * example (requires links.ini to be set up correctly) * // get all the images for product 24 * $i = new DataObject_Image(); * $pi = new DataObjects_Product_image(); * $pi->product_id = 24; // set the product id to 24 * $i->joinAdd($pi); // add the product_image connectoin * $i->find(); * while ($i->fetch()) { * // do stuff * } * // an example with 2 joins * // get all the images linked with products or productgroups * $i = new DataObject_Image(); * $pi = new DataObject_Product_image(); * $pgi = new DataObject_Productgroup_image(); * $i->joinAdd($pi); * $i->joinAdd($pgi); * $i->find(); * while ($i->fetch()) { * // do stuff * } * * * @param optional $obj object the joining object (no value resets the join) * @param optional $joinType string 'LEFT'|'INNER'|'RIGHT'|'' Inner is default, '' indicates * just select ... from a,b,c with no join and * links are added as where items. * @param optional $joinCol string if you want to specifically target a field to join on * useful generally only if you are joining more than once * to the same table and same column using aliases * @param optional $joinAs string if you want to select the table as anther name * usefull when you want to select multiple columsn * from a secondary table. * @return none * @access public * @author Stijn de Reede <sjr@gmx.co.uk> */ function joinAdd($obj = false,$joinType='INNER',$joinCol=false,$joinAs=false) { global $_DB_DATAOBJECT; if ($obj === false) { $this->_join = ''; return; } if (!is_object($obj)) { DB_DataObject::raiseError("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA,PEAR_ERROR_DIE); } $this->_connect(); /* make sure $this->_database is set. */ $this->_get_table(); /* make sure the links are loaded */ $__DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]; $links = array(); if (isset($_DB_DATAOBJECT['LINKS'][$this->_database])) { $links = &$_DB_DATAOBJECT['LINKS'][$this->_database]; } $ofield = false; // object field $tfield = false; // this field /* look up the links for obj table */ if (isset($links[$obj->__table])) { foreach ($links[$obj->__table] as $k => $v) { /* link contains {this column} = {linked table}:{linked column} */ $ar = explode(':', $v); if ($ar[0] == $this->__table) { //START NEW CODE: if ($joinCol !== false) { DB_DataObject::raiseError("joinAdd: You cannot target a join column in the 'link from' table ({$obj->__table}). Either remove the third argument to joinAdd() ($joinCol), or alter your links.ini file.", DB_DATAOBJECT_ERROR_NODATA); return false; } //END NEW CODE $ofield = $k; $tfield = $ar[1]; break; } } } /* otherwise see if there are any links from this table to the obj. */ if (($ofield === false) &&isset($links[$this->__table])) { foreach ($links[$this->__table] as $k => $v) { /* link contains {this column} = {linked table}:{linked column} */ $ar = explode(':', $v); if ($ar[0] == $obj->__table) { /*OLD CODE $tfield = $k; $ofield = $ar[1]; break; */ //START NEW CODE if ($joinCol !== false) { if ($k == $joinCol) { $tfield = $k; $ofield = $ar[1]; break; } else { continue; } } else { $tfield = $k; $ofield = $ar[1]; break; } //END NEW CODE } } } /* did I find a conneciton between them? */ if ($ofield === false) { DB_DataObject::raiseError("joinAdd: {$obj->__table} has no link with {$this->__table}", DB_DATAOBJECT_ERROR_NODATA); return false; } $joinType = strtoupper($joinType); if ($joinAs === false) { $joinAs = $obj->__table; } $objTable = $obj->__table; if ($obj->_join) { $objTable = "($objTable {$obj->_join})"; } $fullJoinAs = ''; if ($obj->__table != $joinAs) { $fullJoinAs = "AS {$joinAs}"; } switch ($joinType) { case 'INNER': case 'LEFT': case 'RIGHT': // others??? .. cross, left outer, right outer, natural..? $this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}". " ON {$joinAs}.{$ofield}={$this->__table}.{$tfield} "; break; case '': // this is just a standard multitable select.. $this->_join .= "\n , {$objTable} {$fullJoinAs} "; $this->whereAdd("{$joinAs}.{$ofield}={$this->__table}.{$tfield}"); } /* now add where conditions for anything that is set in the object */ $items = $obj->_get_table(); if (!$items) { DB_DataObject::raiseError("joinAdd: No table definition for {$obj->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG); return false; } foreach($items as $k => $v) { if (!isset($obj->$k)) { continue; } if ($v & DB_DATAOBJECT_STR) { $this->whereAdd("{$joinAs}.{$k} = " . $__DB->quote($obj->$k)); continue; } if (is_numeric($obj->$k)) { $this->whereAdd("{$joinAs}.{$k} = {$obj->$k}"); continue; } /* this is probably an error condition! */ $this->whereAdd("{$joinAs}.{$k} = 0"); } // and finally merge the whereAdd from the child.. if (!$obj->_condition) { return; } $cond = preg_replace('/^\sWHERE/i','',$obj->_condition); $this->whereAdd("($cond)"); } > -----Original Message----- > From: Alan Knowles [mailto:alan@akbkhome.com] > Sent: Wednesday, August 06, 2003 1:39 AM > To: pear-general@lists.php.net; Andy Crain > Cc: pear-general@lists.php.net > Subject: [PEAR] 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 > > > > > > > -- > PEAR General Mailing List (http://pear.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php