Salesforce SOQL Get Several Fields info from Contact object with Multiple Junction Objects

Question

To all salesforce experts I need some help.

I have a main object called Contact (contact__c), a junction object called Contact Event objec (Contact_Event__c) with Contact object, another junction object called Company Affiliation (Company_Affiliation__c) with Contact object as well.

In my c# code, I need to pass an event Id, to retrieve whoever registered for this event, so multiple contacts info back (the info can be found via contact_event__c by passing event id), the 4 return fields are required:

contact's name, contact's type, contact's company and event name.

To get contact's name and type it's from the main contact object, event name can be from contact event object. The challenge is to get contact's company info which is from company affiliation custom object.

I'm able to use 2 steps to get above info back, but some events have 1000+ contact registered so the performance is bad.

Is there any way to use a single soql to achieve it? like:

SELECT Account_Name__c, Record_Type_Name__c, Is_Primary__c, contact__r.Name__c, Contact__r.Record_Type_Name__c, From Company_Affiliation__c where Record_Type_Name__c='Business' and Is_Primary__c = true and Contact__r.Guid__c in (Select Guid from Contact where ...)

or

SELECT Event_Name__c, Contact__r.Guid__c,Contact__r.Name__c,Contact__r.Record_Type_Name__c,(Select Account_Name__c from Company_Affiliation__c Where...) FROM Contact_Event__c WHERE Event__r.CVent_ID__C = '83B88111-15D4-4125-B9A0-F66FE46E26D8'...

Below query works (Thanks so much @eyescream) but I have to use Event as my main object to pass the event's guid, not Contact as the main object.

SELECT Id, Guid__c, name, (SELECT Id, Guid__c, AccountName__c FROM Company_Affiliations__r where Record_Type_Name__c='Business' and Is_Primary__c = true), (SELECT Id, Guid__c,Name, Event_Name__c FROM Contact_Events__r WHERE Event_External_ID__c = 'E2469609-12E1-48C4-ABFF-D617C87D5EC7') FROM Contact WHERE Guid__c = '30303334-4d30-3030-3032-466d4a6c6f00'

Thanks so much

Answer 1

What are the relations exactly? Contact + 2 related lists (contact__c -> Contact_Event__c, contact__c -> Company_Affiliation__c)? Or top-down (contact__c -> Contact_Event__c -> Company_Affiliation__c)

Assuming it's 1st, you should be able to get it in one go

SELECT Id, Name, Name__c, Record_Type_Name__c, Guid__c, 
    (SELECT Id, Name, Event_Name__c
    FROM Contact_Events__r
    WHERE Event_ID__c = '83B88111-15D4-4125-B9A0-F66FE46E26D8'),
    (SELECT Id, Name, Account_Name__c, Record_Type_Name__c, Is_Primary__c
    FROM Company_Affiliations__r
    WHERE Record_Type_Name__c='Business' and Is_Primary__c = true)
FROM Contact__c
WHERE Id IN (SELECT Contact__c FROM Contact_Event__c WHERE Event_ID__c = '83B88111-15D4-4125-B9A0-F66FE46E26D8')

You'll need to check your relationship names (related list names) in Setup or using "describe" calls but this should be close to what you need.

If the performance is bad - is the Event_ID__c marked as external id, unique?