0

Morning guys, I am having a issue, I need to make a inner join in soql... Example:

  • Process instance( table1)
  • Process instance step(table 2)

I need to create a table mixing both tables, Id tabel 1 is process instance id in the 2, Table 1 primary key is a forien Key in table 2...

But soql will not support joins for tables I have to write a complete code for this... do you know any other way apart form Apex class?

  • When you mean forieign key do you mean lookups? – Pranay Jaiswal Nov 16 '18 at 10:02
  • Yes @pranay Jaiswal – Monica Henao Nov 16 '18 at 10:18
1

So Lets Assume this is your Process Table 1

Id(Primary Key),Name,Description__c,Other_Fields__c

And Process Table 2 Schema Looks like this

Id(Primary Key),Name,Process_Table1__c(Lookup / foreign Key to Table 1), Other_Fields__c

Here you can use Join, to get Parent_table1__c Fields querrying the Process_Table2__c, Its called as child-to-parent traversal

SELECT Id,Name,Process_Table1__r.Name,Process_Table1__r.Description FROM Process_Table2__c

Also, Another kind of join is Parent To child, Inner Querry, which allows yoqueryuerry child for a particular parrent

SELECT Id,Name,Description__c,(SELECT Id,Name FROM Process_Table2__r) FROM Parent_table1__c

Src: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

| improve this answer | |
  • I tried: 1. SELECT ProcessInstance.*, (SELECT * FROM ProcessInstanceStep) From ProcessInstance; But It will give parsing error – Monica Henao Nov 16 '18 at 22:23
  • 2
    Select * doesn't work in apex, you To specify field names – Pranay Jaiswal Nov 16 '18 at 22:23
  • I put in prosessInstanceId, It says relational objest don't support – Monica Henao Nov 16 '18 at 22:30
  • @MonicaHenao Check out the relationship name. it is possible that you specified the name incorrectly. The relationship name is NOT always __r, and pretty much NEVER __c – zaitsman Oct 3 '19 at 23:59
1

I would read up on relationships in SOQL because the concept of a JOIN as it works in a regular SQL database doesn't really conform to how SOQL works.

If you want ProcessInstances and their related Steps, you can run a query like this:

SELECT Id
,CompletedDate
,ElapsedTimeInDays
,ProcessDefinition.Name
,Status
,TargetObject.Type
,TargetObjectId
,(
    SELECT Id
        ,ActorId
        ,Actor.Name
        ,Comments
        ,CreatedById
        ,CreatedDate
        ,ElapsedTimeInDays
        ,IsDeleted
        ,IsPending
        ,OriginalActorId
        ,OriginalActor.Name
        ,ProcessInstanceId
        ,ProcessInstance.Status
        ,ProcessNodeId
        ,ProcessNode.Name 
        ,RemindersSent
        ,StepStatus
        ,TargetObjectId
    FROM StepsAndWorkitems
    ORDER BY CreatedDate DESC, Id DESC
)
FROM ProcessInstance
LIMIT 50

That will retrieve a Process Instance with all of it's "children".

| improve this answer | |

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.