Re: 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 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