Re: DataObject extension: getCrossLink()

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);

}