Apex Code Development You need to sign in to do that
Don't have an account?
lashak Mimic a Join on User.Id and Opportunity.OwnerId
I am stuck. We are moving a process that was going after a local database that was replicating our salesforce data to go directly after salesforce. The problem I am having is how to mimic the following PHP code in regards to a join on the User and Opportunity objects:
function getSalesforceProspects()
{
$msquery="select Opportunity.sfOpportunityId as SFXRefID
from
Account
inner join Opportunity on Opportunity.AccountId = Account.sfAccountId
inner join Users on Users.sfUserId = Opportunity.OwnerId
where
-- 'Channel Sales & Marketing Opportunity' and 'Direct Sales & Marketing Opportunity' record types:
Opportunity.RecordTypeId in ('0123000000056kxAAA', '012300000004zjnAAA') ORDER BY Opportunity.OpportunityName";
$result = mssql_query($msquery);
/* Error occurred, return given name by default */
if(!$result || (mssql_num_rows ($result) < 1)){
return NULL;
}
/* Return result array */
while($row = mssql_fetch_array($result)){
$dbarray[]=$row['SFXRefID'];
}
return $dbarray;
} // end func
I am new to SOQL and I have been trying to figure out a way to mimic the above query with no luck. Any help will be greatly appreciated.
Thanks,
SOQL has no "AS" operator, and no inner join. Joins in SOQL are done via relationships, so the syntax is simpler. I'd recommend you download the Force.com IDE -- in it there's a handy query builder that can help you build and test your queries. Connect it to your Salesforce.com instance and then double click the icon that says "salesforce.schema" and you'll get the query builder.
To your question, though, you use relationships like this:
Select o.Owner.Username, o.OwnerId, o.Account.Name, o.AccountId From Opportunity o where o.RecordTypeId in ('0123000000056kxAAA', '012300000004zjnAAA') ORDER BY o.Name
So you see, the OwnerId relationship is just "Owner," the Account relationship is "Account." You can find the other relationship names using that query builder I mentioned. Via these relationships you have access to all the fields.
The IN clause should work OK provided you refer to the Opportunity correctly, as should the ORDER BY clause. Those are analogous to SQL.
Thank you. I now see how to get down to the references. The Force.com IDE is great and will be used quite often now.
I appreciate the quick response you gave.