Re: Re: ON clause with DataObject

Probably best to hand build the join -> write direct to $this->_join I dont think making the join code to complex will acheive much, other than create more bugs ;) Regards Alan Justin Patrin wrote:

On 2/8/06, Alan Knowles <alan@akbkhome.com> wrote:

looks suspiciously like a bug - do you fancy digging into the joinadd code and working out where that's gone wrong.

I know exactly what's wrong. In this case, he's doing the join with the "problem" *before* the join which names the table as 'subcat'. For this case to work you'd need to cache the join requests and build the SQL later. A good idea, but very complicated IMHO. Then again, I can't think of a way for this to work right as that first join does need to be first (right?) because that table is then joined to another. I think the only way to make this work is to build the query on demand.

Regards Alan On Thu, 2006-02-09 at 02:14 +0200, Abbas Adel wrote:

Dears, I am using DataObject to join 2 tables; book, book_cat. Using self join with the book_cat table. The problem is in (ON) clause: It shoud be

     cat.id=subcat.parent_id

Instead of

     cat.id=book_cat.parent_id

I don't know why book_cat.parent_id is copied exactly from links.ini without change the table name? The links.ini file contains: -------------------------------------------------------- [book] category = book_cat:id [book_cat] id = book_cat:parent_id -------------------------------------------------------- Code: -------------------------------------------------------

$cat    = DB_DataObject::factory('book_cat');

$subcat = DB_DataObject::factory('book_cat'); $book = DB_DataObject::factory('book'); $subcat->joinAdd($cat, 'LEFT', 'cat'); $book->selectAs(); $book->joinAdd($subcat, 'INNER', 'subcat'); $book->selectAs($subcat, 'subcat_%s', 'subcat'); $book->selectAs($cat, 'cat_%s', 'cat'); ------------------------------------------------------- The result query: ------------------------------------------------------- SELECT book.book_id as book_id , book.auther as auther , book.isbn as isbn , book.name as name , book.year as year , book.copy as copy , book.volume as volume , book.category as category , subcat.id as subcat_id , subcat.parent_id as subcat_parent_id , subcat.name as subcat_name , cat.id as cat_id , cat.parent_id as cat_parent_id , cat.name as cat_name FROM book INNER JOIN book_cat AS subcat ON subcat.id=book.category LEFT JOIN book_cat AS cat ON cat.id=book_cat.parent_id ------------------------------------------------------- The on clause should be (ON cat.id = subcat.parent_id) in order to work properly. Thanks BISO

-- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

-- Justin Patrin