Re: writing Joins using DB_DataObject 1.7.10

On Mon, 21 Mar 2005 18:23:56 +0530, Kurund Jalmi <kurund@webaccess.co.in> wrote: > hi all > > i also tried manupulating > > $this->_join, but still not working? i am nor sure if method i used was > correct.. tables are same as below.. > > can anyone help me please... > any instructions how to manupulate $this->_join ??? Well, as I said before, DB_DataObject doesn't support adding clauses to the ON clause directly. I proposed a patch which would add this, but Alan wants to do it some other way...not sure how. I'd say to use my patch, but my server's HD crashed, so I'm now missing all of my patches. (argh!) I use a preg_replace to manipulate my join clause in one case. You may also be able to use str_replace. You should look at the output of $this->_join after you do the joinAdd then figure out how to alter it. Here's some code for the one I did: $locker->_join = preg_replace('/LEFT\s+JOIN\s+order\s+ON\s+order.dropLocationLocker_id=dropLocationLocker.id/', 'LEFT JOIN order ON order.dropLocationLocker_id=dropLocationLocker.id AND orderStatusOption_id <> 10', $locker->_join); Of course, this makes very little sense without its context.... :-| If you need more help post the contents of $this->_join after the joinAdd call and what you want it to look like and we can help you further. > > regards > > kurund jalmi > > Kurund Jalmi wrote: > > Thanks all, > > > > "LEFT" worked fine for me.. (instead of LEFT OUTER JOIN) > > BUT still have problem writing multiple condition for "addJoin" > > > > i have 3 tables > > main, location and place > > i created object for those three tables.. > > > > i did the following things. > > > > $location->joinAdd($place); > > $location->whereAdd($this->tableName().'.id = 1'); > > > > $main->joinAdd($location); > > $main->whereAdd($main->tableName().'.id = > > '.$location->tableName().'.id'); > > > > after firing the sql.. > > > > when i checked my logs i found that.. condition for 'ON' clause was in > > 'WHERE' clause > > > > it doesnnot adds to 'ON' clause of that join.. > > > > > > regards > > > > kurund jalmi > > > > Justin Patrin wrote: > > > >> On Sat, 19 Mar 2005 19:39:39 +0530, Kurund Jalmi > >> <kurund@webaccess.co.in> wrote: > >> > >>> hello, > >>> > >>> i am working on DB_DataObject 1.7.10. > >>> > >>> i have few querries on writing joins: > >>> > >>> 1. How can i write "left outer joins" using DB_DataObjects? i found out > >>> that addjoin has only 3 options "LEFT, RIGHT AND INNER".. > >> > >> > >> > >> LEFT is the same as LEFT OUTER. > >> > >> > >>> 2. If i want to write condition in ON clause of JOIN, i use the follwing > >>> method: > >>> > >>> $this->joinAdd($location); > >>> $this->whereAdd($this->id = $location->id); > >>> > >>> this works fine. > >> > >> > >> > >> I doubt it, that's not the right syntax at all. This should work: > >> > >> $this->whereAdd($this->tableName().'.id = > >> '.$location->tableName().'.id'); > >> > >> > >>> but if i want to write multiple condition in ON clause > >>> > >>> for example: > >>> $this->joinAdd($location); > >>> $this->whereAdd($this->id = $location->id AND $location->state = 1); > >>> > >>> then only first condition is considered.. second is ignored.. > >>> > >> > >> > >> Again, probably because that's not the right syntax at all. > >> > >> $this->whereAdd($this->tableName().'.id = > >> '.$location->tableName().'.id AND > >> '.$location->tableName().'.state = > >> 1'); > >> > >> Of course, adding a where isn't quite the same as actually adding to > >> an on clause, especially when the join is OUTER. If you actually want > >> to add to the ON clause you'll have to edit $this->_join manually. > >> This is somewhat hard to do, though, especially if you're doing > >> multiple joins. > >> > -- Justin Patrin