Select from two tables

lachof · #1 01-08-2008, 03:08 PM

Hello all!

I am trying to get the product and standard price but i only get the ID value.

Here is the code:

PHP Code:


			
public function GetProductDescriptionAndPrice($ProductId)
        {                       
            session_start();
            $location = $_SESSION['location'];
            $sessionId = $_SESSION['sessionId'];
            $wsdl = $_SESSION['wsdl'];
            $username = $_SESSION['usr']; 
            $password = $_SESSION['pwd']; 
            session_write_close();   
        
            $client = new SforcePartnerClient();
            $sforceSoapClient = $client->createConnection('../'.$wsdl);
            $client->setEndpoint($location);
            $client->setSessionHeader($sessionId);
            $loginResult = $client->login($username, $password);
            
            //Get Description and Price
            $query = "SELECT Id, Name, Family, IsActive, Description, ProductCode,  (SELECT  UnitPrice FROM PricebookEntries) FROM Product2 where id = '".$ProductId."'";
        
            $queryResult = $client->query($query);
            $records = $queryResult->records;    
            
            $counter = 0;
            foreach ($records as $record) 
            {                
                $sObject = new SObject($record);        
                $Id = $sObject->Id;
                $Name = $sObject->fields->Name;
                $Description = $sObject->fields->Description;
                $ProductCode = $sObject->fields->ProductCode;                                
                $qr = $sObject->queryResult[0]->records;                    
                foreach ($qr as $record2) 
                {
                        $UnitPrice =  $record2->fields->UnitPrice;
                }
                $counter++;           
            }
            $ProdArr = array();
            $ProdArr['Description'] = $Description;
            $ProdArr['UnitPrice']= $UnitPrice;
            $ProdArr['Id']= $Id;    
            return $ProdArr;
        }

What am i doing wrong?
Is there a better way to do this?

Thnx in advance!!

mike · #2 01-08-2008, 03:31 PM

Isn't it PriceBookEntry, Not pricebookentries

ALSO have you tried your SOQL in the Data Loader to make sure it works or the sforce_explorer @ sforce @ sourceforge to see if it works? I use it for SOQL to make sure it works before I code it into a script

~Mike

lachof · #3 01-08-2008, 06:46 PM

Thnx for your response Mike.

I use Apex Explorer 8.0 and strangely the query works as posted. If i chnage the query to select form priceBookEntry i get this error:

Quote:

Didnt Understand relationship 'PriceBookentry' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropiate names.

What i did? I used two separate queries. I know I know not the best way to do it but i intend to fix it once i get a test working. I'll post the correct way when I have it.

Lachof

toivo · #4 03-22-2008, 06:07 PM

In a similar relationship query I would expect results from this:

Quote:

$queryResult = $sObject->queryResult[0];
$qr = $queryResult->records;
foreach ($qr as $record2) {
$UnitPrice = $record2->fields->UnitPrice;
}

You could use one of the following to see the structure of the object:

Quote:

print_r(get_object_vars($queryResult));

var_dump($queryResult);

Regards,
toivo