function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

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
    inner join Opportunity on Opportunity.AccountId = Account.sfAccountId
    inner join Users on Users.sfUserId = Opportunity.OwnerId

    -- '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)){
  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.




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 IDE -- in it there's a handy query builder that can help you build and test your queries.  Connect it to your 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.

And of course I presume you're going to use the PHP Toolkit to actually run this query.

Thank you.  I now see how to get down to the references.  The IDE is great and will be used quite often now.


I appreciate the quick response you gave.