Re: ON clause with DataObject

On 2/8/06, Abbas Adel <biso@tantawi.net> 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. > That's a really tricky thing to get right. Part of the problem here is that the join with the ON clause that you're saying should have a different name is done in your code *before* the join which gives the 'subcat' name for that object. There's no way this would work as DB_DO is right now. There is also no way (AFAICT) that this can work with DB_DO as it is now. I suggest changing 'subcat' to be joined as 'book_cat' (i.e., no joinAs parameter). That should work just fine. -- Justin Patrin