Re: DataObject extension: getCrossLink()

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