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

Alan, Looks great and works fine for me. Good call switching the parameter order. Not only does it preserve backward compatibility, but it just makes more sense too: I don't think you'd ever try and succeed in targeting a column without first aliasing tables, although you might wish to alias tables without targeting a column. Andy > -----Original Message----- > From: Alan Knowles [mailto:alan@akbkhome.com] > Sent: Wednesday, August 06, 2003 10:24 PM > To: Andy Crain > Cc: pear-general@lists.php.net > Subject: Re: [PEAR] Re: How to do multiple joins to the same table using > joinAdd() in DB_DataObject > > > I've flipped the joinAs/JoinCol arguments - It looks like that would > retain Backwards Compatibility.. Let me know if it works > > ���4��$��P+ > �http://cvs.php.net/co.php/pear/DB_DataObject/DataObject.php?r=1.116 > > Regards > Alan > > Andy Crain wrote: > > 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 > > > > > > > > > > > > > -- > Can you help out? > Need Consulting Services or Know of a Job? > http://www.akbkhome.com