Mysql query to SQLBuilder

MySchizoBuddy · April 2007

ur help will result in some cool extensions being build. I know u want cool extensions right :P

I need to optimize 3 calls to the database with just one call. I know i have to use the JOIN statement for this. here are the 3 calls

$sql = $this->Context->ObjectFactory->NewContextObject($this->Context, 'SqlBuilder');
            $sql->SetMainTable('Discussion','d');
            $sql->AddSelect(array('Name','AuthUserID','Closed','DateCreated','CountComments' ), 'd');
            $sql->AddWhere('d','DiscussionID','',$DiscussionID,'=');
            $result = $this->Context->Database->Select($sql, $this->Name, 'DiscussionStuff', 'An error occurred while listing the discussion information.');
            $Discussion = $this->Context->Database->GetRow($result);
            
            $sql->Clear();
            $sql->SetMainTable('User','u');
            $sql->AddSelect('Name', 'u');
            $sql->AddWhere('u','UserID','',$Discussion['AuthUserID'],'=');
            $result = $this->Context->Database->Select($sql, $this->Name, 'GetAuthorName', 'An error occurred while listing the discussion information.');
            $rows = $this->Context->Database->GetRow($result);
            $Discussion['AuthUsername'] = $rows[0];
            
            $sql->Clear();
            $sql->SetMainTable('Comment','c');
            $sql->AddSelect('Body', 'c');
            $sql->AddWhere('c','DiscussionID','',$DiscussionID,'=');
            $result = $this->Context->Database->Select($sql, $this->Name, 'GetCommentBody', 'An error occurred while listing the discussion information.');
            $rows = $this->Context->Database->GetRow($result);

MySchizoBuddy · May 2007

Anyone?

lech · May 2007

It might be best to also explain why you're fetching all those rows for what purpose so there could be a little bit of context to what is meant to be built by those queries.

MySchizoBuddy · May 2007

its for a permalink page So I need 1) Name of Discussion, I need to query the Discussion table for that 2) User ID and Username. Hence i need to query the User table to get the username 3) First Comment of the Discussion, so I need to query the Comments table as well Now the AuthUserID in the Discussions table is same as UserID in the User table. So they can be joined. DiscussionID is same in the Discussion table and Comment table, so they can joined as well