Re: DataObject extension: getCrossLink()

Ok, have added this to the code, and released version 0.8 I also added some code to try and do 2 way link checking.. - I've no idea if this works or what it produces - if you could run it through some tests it would help. as you said, since the join only added 1 line to find, findJoin is not really required, however I suspect that It may be neccessary to modify the _build_condition code to check if _join is defined, and hence produce conditions like {$this->__table}.{$k}= $this->$k; rather than just {$k}= $this->$k let me know how you get on. Regards Alan

Ok, here I am again, with a new and improved version of the joinAdd() method :). I rewrote the method so that is looks and works more like the whereAdd(), orderBy(), etc methods. Continuing with the product/images example, here's how you would use it now: -------------------- in the database.links.ini: ; many to 1 joins are defined below

[product_image]        ; the name of 'magic' joining table

product_id = product:id ; the column name that joins with table:column

image_id = image:id    ; the column name that joins with table:column

in the php file: $image = new DataObjects_Image(); $productimage = new DataObjects_Product_image(); $productimage->product_id = 1; OR $image->whereAdd('product_image.product_id = 1'); $image->orderBy('product_image.sort'); $image->joinAdd($productimage); $image->findJoin(); SQL executed: SELECT * FROM image

    INNER JOIN product_image
    ON product_image.image_id=image.id
    WHERE product_image.product_id = 1
    ORDER BY product_image.sort

-------------------- Actually the 'product_id = product:id' line in the database.links.ini isn't necessary here, but it might be if you were to join the other way around. Alan was indeed right before when he mentioned that the query had little to do with the object it was executed on. This has improved a bit now, as it is executed on the object it should return. The whereAdd(), orderBy(), etc methods are not executed on the joining object ($productimage) because it would be difficult to switch these to the other object ($image) (the joining table name should be auto-prepended then). But it is possible to set a column of the joining table to a certain value, and where statements will automagically be added when joinAdd() is executed (uses a modified version of _build_condition()). On a side note, these Where statements are not removed if you set the join to nothing (with $obj->joinAdd()). It would in theory be possible to add multiple joins to an object, although I'm not sure when you would use this, as in most cases the query would be so specific you would be better off writing your own query or method. The only difference between the findJoin() and find() method is the query building: -------------------- @@ -265,6 +265,7 @@

         $this->_query("SELECT " .
             $this->_data_select .
             " FROM " . $this->__table . " " .
+            $this->_join . " ".
             $this->_condition . " ".
             $this->_group_by . " ".
             $this->_order_by . " ".

-------------------- So I think these two could be merged and make for a single find() method. This would also integrate the joinAdd() method more with the selectAdd(), whereAdd(), orderBy(), etc methods. I guess this could be a good addition to the DataObject class, if there are no further suggestions on improving the way it works. Stijn Alan Knowles wrote:

Below are the notes I've added to the TODO list. - including a brief note on the reservations for doing it as you've shown... - As you can see from the notes, I've just focused on documenting a) the end usercode that would be used b) the resulting SQL. I've not actually tested it on an sql server- I'll have to dig up a database that is dependant on this type of cross linking/joining to test the concept . Regards Alan ---------------------------------- Issue: Crosstable Joins Althought the main documents indicate that a join version ended up being too complex, this was based on doing multiple joins on many tables. - It has been suggested (and code provided), that doing a simpler version may be possible: One suggestion by Stejn de Reede was to do this $product = new DataObjects_Product(); $product->get(24); $img = new DataObjects_Image(); $img->orderBy('product_image.sort'); $product->getCrossLink($img); which would result in the queries: SELECT * FROM produce WHERE id = 24; SELECT * from image INNER JOIN product_image ON product_image.image = image.id WHERE product_image.product_id = 24 ORDER BY product_image.sort; ** Reservations: * introduction of 'magic table' product_image, that is not clear from the code. * use of assumed defaults like id and column names product_id which are 'magicly generated' - The initial links code used this, and proved slightly troublesome - the links.ini file solved alot of this. * query results have little relation to the object that they are exectuted on. (eg. product object is filled with image and product_image join results) From a totally abstract view (thinking out loud), It would be clearer to do something like:

$image              = new DataObjects_Image;
$productimage       = new DataObjects_Productimage

$productimage->productid = 24; $image->addJoin( $productimage ); $image->orderBy('productimage.sort') $image->findJoin(); Which would build: SELECT * from image INNER JOIN productimage ON productimage.image = image.id

     WHERE productimage.productid = 24

ORDER BY productimage.sort; $productimage = new DataObjects_Productimage;

$product      = new DataObjects_Product;
$image        = new DataObjects_Image

$product->id = 24; $productimage->addJoin( $image ); $productimage->addJoin( $product ); $productimage->orderBy('productimage.sort') $productimage->findJoin(); Which would build: SELECT * from productimage INNER JOIN image ON productimage.image = image.id INNER JOIN product ON productimage.product = product.id WHERE product.id = 24 ORDER BY productimage.sort; ** I've no idea is this query would work or is valuable - so it needs a bit of testing. Based on a configuration: (joins.ini) // or could be shared with links.ini ? [productimage] ;; many to 1 ;; joins table productimage:image to image:id image = image:id ;; joins table productimage:product to product:id product = product:id Stijn de Reede wrote:

Hi all, I'm a really satisfied user of the DataObject module, and I think it works great. It's a real relief not having to (re)write all those get() and set() methods for each object you want to use, and when you change you database design a little (ie, adding a column). However, there was one shortcoming which seemed easy to resolve to me: the DataObject lacked a simple getCrossLink method to fetch DataObjects related in a n-to-m way with the current DataObject. I know the PEAR manual states that (most) join queries are too complex to get them into a generic method, and only supplies a getLink() and getLinks() method to fetch 1-to-n related objects (which works great btw). In my current project I'm using the n-to-m relationship a lot, for example with images related to products, productsubgroups and productgroups. So, I've writted a simple method to fetch these objects for me. I've mailed about it with Alan Knowles, and he said he was interested in seeing my solution. So, here it is, for all of you to review, comment and maybe even use. Stijn ------------------------------------------------------------------------ /** * Builds and executes a join query to fetch DataObjects related in an * n-to-m way with this DataObject * * You can add your own selectAdd, whereAdd, groupBy, orderBy and limit * options to the object you pass as an argument. * * example: * * table: product * columns: id, name, description, price * table: image * columns: id, name, url, width, height * table: product_image * columns: product_id, image_id, sort * * $product = new DataObjects_Product(); * $product->get(24); * $img = new DataObjects_Image(); * $img->orderBy('product_image.sort'); * $product->getCrossLink($img); * $product->images = array(); * while($img->fetch()) {

   *     array_push($product->images, $img);

* } * * The SQL query executed is: * SELECT * * * FROM image * INNER JOIN product_image ON product_image.image_id=image.id * WHERE product_image.product_id=24 * ORDER BY product_image.sort * *

   * @param    &$obj       object  the DataObject to which this
   *                               DataObject is related
   * @param    $join       string  the join type to be used,
   *                               defaults to INNER
   * @param    $link_table string  the linking table, defaults to
   *                               $this->__table.'_'.$obj->__table
   * @param    $parent_id  string  the columnname of the parent id in the
   *                               linking table, defaults to
   *                               $this->__table.'_id'
   * @param    $child_id   string  the columnname of the child id in the
   *                               linking table, defaults to
   *                               $obj->__table.'_id'

* @return none * @access public

   * @author   Stijn de Reede      <sjr@gmx.co.uk>

*/ function getCrossLink(&$obj, $join='INNER', $link_table=null, $parent_id=null, $child_id=null) {

       if ($link_table == null) $link_table = $this->__table.'_'.$obj->__table;
       if ($parent_id  == null) $parent_id  = $this->__table.'_id';
       if ($child_id   == null) $child_id   = $obj->__table.'_id';
       $parent_table = $this->__table;
       $child_table = $obj->__table;
       $obj->whereAdd("$link_table.$parent_id=$this->id");
       $sql = "SELECT ".
           $obj->_data_select .
           " FROM ".$obj->__table .
           " $join JOIN $link_table ON $link_table.$child_id=$child_table.id ".
           $obj->_condition." ".
           $obj->_group_by." ".
           $obj->_order_by." ".
           $obj->_limit;
       $obj->query($sql);

}

------------------------------------------------------------------------ /**

   * @param    $obj    object      the joining object

* @return none * @access public

   * @author   Stijn de Reede      <sjr@gmx.co.uk>

*/ function joinAdd($obj = false) {

       if ($obj === false) {
           $this->_join = '';
           return;
       }
       $this->_get_table();
       $links = &PEAR::getStaticProperty('DB_DataObject', "{$this->_database}.links");
       if (!isset($links[$obj->__table])) {
           DB_DataObject::raiseError("joinAdd: $obj->__table has no defined links", DB_DATAOBJECT_ERROR_NODATA);
           return false;
       }
       $field = false;
       foreach ($links[$obj->__table] as $k => $v) {
           $ar = explode(':', $v);
           $table = $ar[0];
           $field = $ar[1];
           if ($table == $this->__table) {
               break;
           }
           $field = false;
       }
       if ($field === false) {
           DB_DataObject::raiseError("joinAdd: $obj->__table has no link with $this->__table", DB_DATAOBJECT_ERROR_NODATA);
           return false;
       }
       $this->_join .= "INNER JOIN $obj->__table ON $obj->__table.$k=$table.$field ";
       $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("$obj->__table.{$k} = '" . addslashes($obj->$k) . "'");
               continue;
           }
           if (is_numeric($obj->$k)) {
               $this->whereAdd("$obj->__table.$k = {$obj->$k}");
               continue;
           }
           /* this is probably an error condition! */
           $this->whereAdd("$obj->__table.$k = 0");
       }

} function findJoin($n = false) {

       if (!$GLOBALS['_DB_DATAOBJECT_PRODUCTION']) {
           $this->debug($n, "__find",1);
       }
       if (!$this->__table) {
           echo "NO \$__table SPECIFIED in class definition";
           exit;
       }
       $this->N = 0;
       $tmpcond = $this->_condition;
       $this->_build_condition($this->_get_table()) ;
       $this->_query("SELECT " .
           $this->_data_select .
           " FROM " . $this->__table . " " .
           $this->_join . " ".
           $this->_condition . " ".
           $this->_group_by . " ".
           $this->_order_by . " ".
           $this->_limit); // is select
       ////add by ming ... keep old condition .. so that find can reuse
       $this->_condition = $tmpcond;
       if (!$GLOBALS['_DB_DATAOBJECT_PRODUCTION']) {
           $this->debug("CHECK autofetchd $n", "__find", 1);
       }
       if ($n && $this->N > 0 ) {
           if (!$GLOBALS['_DB_DATAOBJECT_PRODUCTION']) {
               $this->debug("ABOUT TO AUTOFETCH", "__find", 1);
           }
           $this->fetchRow(0) ;
       }
       if (!$GLOBALS['_DB_DATAOBJECT_PRODUCTION']) {
           $this->debug("DONE", "__find", 1);
       }
       return $this->N;
   }