Re: Re: ON clause with DataObject

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