How do I write a soql child query using the new partner object

Question

I need to pull from a custom object and where account (look up field) where partner (child object) role = x

Original query:

select id, account
from customobject

My thought for the partner:

select id, account
from customobject
where account in (
 select id from account where id in (
   select accountfromid from partner where role = 'x'))

Is there any way of doing this or do I just need to do a separate query from the partner object then query the custom object with account ids in (x)?

Answer 1

With the following assumptions in mind:

• "customobjet" has a relationship field (master-detail or lookup) to Account
• "partner" also has a relationship field (master-detail or lookup) to Account

You're not too far off. What you're looking to do here is called a Semi-Join (about the second half of that page).

[
    SELECT Id
    FROM CustomObject__c
    // Both the LHS (left-hand side) and RHS (right-hand side) must not traverse 
    //   relationships, and they must be Ids of the same SObject type (i.e. no 
    //   mixing Account and Contact Ids)

    //    v LHS can be a lookup field
    WHERE Account__c IN (SELECT Account__c FROM Partner__c WHERE Role__c = 'x')
    //                          ^ RHS can also be a lookup field

]