Re: Re: ON clause with DataObject

On 2/8/06, Justin Patrin <papercrane@gmail.com> 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. > Or...alternatively...allow a joinAs for the current DO as well as the one being joined...it would only affect the name of the table in the query and should only be used if that table is being further joinAdd'ed. > > 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 > -- Justin Patrin