1

How can I use SOQL to join the AccountShare, Account & User objects to fetch matching data?

Here are my attempts so far

select id, accountid, accountaccesslevel, rowcause 
from accountshare 
where userorgroupid in (select id from user where name = 'john')
and accountid in (select id from account where recordtype.name =  'foo')

This works fine.

Questions

  1. How can I do a traditional (inner) join by moving the User and Account objects into the FROM clause so I can retrieve information from them? I tried using the obvious SQL syntax but got a "Unknown error parsing query" in the Developer console.

  2. How can I do the same thing using the REST API?

Thanks

  • You can get most of fields from Account or User by including them in your SOQL like this: select id, accountid, Account.name, userorgroup.name, accountaccesslevel, rowcause from accountshare ... – o-lexi Jun 17 '16 at 17:16
  • You can only have one object in your FROM clause. You can, however, incorporate parent data into your SELECT clause. – Adrian Larson Jun 20 '16 at 15:51
1

If there is a relationship defined between the objects, you can include parent or child records using the relationship name, as described in detail here

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL.htm

However, you cannot perform "arbitrary SQL joins" as indicated here https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm

The accountid and userorgroupid fields on AccountShare are relationship fields, so you can pull in fields from the parent objects via dot notation.

This query for example works

SELECT AccountId,Id, Account.name, userorgroup.name FROM AccountShare LIMIT 10

I tried a few other user fields such as email, isactive and they worked as well.

You can find the relationship name by examining the object in workbench.developerforce.com and going to info > standard and custom objects as in the screenshot below

workbench

Running query via REST API should be very similar. You can test by using the 'REST explorer' under utilities in workbench. The information on the query resource in the REST API developer guide has details.

Hope that helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.