Link to home
Get AccessLog in
Avatar of bakum
bakum

asked on

Understanding relationship queries in Salesforce SOQL

Hi,

I have a custom object, Logistics_Event__c, which has a field which is a lookup to another custom object, Course__c. (I'm doing this with the PHP toolkit, by the way)

I want to query my Logistics Events and return fields from Course__c: Name and Version__c.

I've tried two ways with no luck:
SELECT
            l.Id,
            l.Start_Date__c,
            l.Number_of_Days__c,
            l.Location__c,
            Course__r.Name,
            Course__r.Version__c
            FROM Logistics_Event__c l

Gives me no error, but doesn't return any info.  There is not ALWAYS a course ID on the Logistics_Event__c record, but there is about 50% of the time.  I would have thought it would return nothing sometimes and the field data when there's data to return.

I also tried this way:
SELECT
            l.Id,
            l.Start_Date__c,
            l.Number_of_Days__c,
            l.Location__c,
            (SELECT Name, Version__c FROM Course__c)
            FROM Logistics_Event__c l

ERROR: Didn't understand relationship 'Course__c' in FROM part of query call

Can someone illuminate me?

Thanks!

-mb
Avatar of MonkeyPushButton
MonkeyPushButton
Flag of United Kingdom of Great Britain and Northern Ireland image

Could you post the PHP code that processes the output of this query please?
ASKER CERTIFIED SOLUTION
Avatar of pauldesatadvologixdotcom
pauldesatadvologixdotcom

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of bakum
bakum

ASKER

I attached a PHP snippet.  

As for Course__r vs. Course__c, the docs say you should use __r instead of __c when specifying a relationship in a query.  However, just for grins I tried __c and it didn't work.  Same error.  

Driving me nuts...
$query = "SELECT
            l.Id,
            l.Start_Date__c,
            l.Number_of_Days__c,
            l.Location__c,
            Course__r.Name,
            Course__r.Version__c
            FROM Logistics_Event__c l";
 
		echo "<!-- $$$$$ query = ".$query." -->";
		
		try 
		{
		$options = new QueryOptions(2000);    
		$mySforceConnection->setQueryOptions($options);
		$queryResponse = $mySforceConnection->query($query);
		$queryResult = new QueryResult($queryResponse);
 
	    if ($queryResult->size == 0) return FALSE;
				
		$records = $queryResult->records;
		
		$total_number_of_updates = 0;
		
		foreach ($records as $record) 
		{
		   $eventID = (string)$record->Id;
		   //grab the event type so that we can classify events now,
		   //in order to spit them out later clustered together by event type
 
			foreach($record->fields as $key => $value)
			{
				$event[$eventID][$key] = (string)$value;
				$event[$eventID]['theater']=$theater;
				$event[$eventID]['acronym']=$acronym;				
				$event[$eventID]['type']=$type;
			}
			if ($record->sobjects)
			{
				foreach ($record->sobjects as $subrecords)
				{
					for ($i=0; $i++; $i < count($record->sobjects))
					{
						foreach($record->sobjects[$i]->fields as $key => $value)
						{
							$event[$eventID][$key] = (string)$value;
						}
					}
				}
			}
		}
		
		
		
		}
		catch (Exception $e)
		{
			print_r($mySforceConnection->getLastRequest());
			echo $e->faultstring;
		} 
		
		return $event;

Open in new window

Avatar of bakum

ASKER

turns out the first way was the right way, I was iterating through the results incorrectly.  Partial points!