6

I am having problems creating a SOQL query over my 3 objects.

Contact, Patient and Disease

Patient and Disease are in a Master Detail relationship, where Disease is the Master

Contact is a Lookup field in Patient.

So, the query that I need is: Select Id from Contact, where patient is that contact and has disease x

I need this query to populate the list targetObjects in this code:

List <Id> targetObjectIds = new List <Id> ();
List <Contact> targetObjects = new List <Contact> ();

targetObjects = [SELECT Id FROM Contact];
for(Contact cnt : targetObjects) {
  targetObjectIds.add(cnt.Id);
}

Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage();
mail.setTargetObjectIds(targetObjectIds);

Tia.

1
  • Can you please describe your question with an example ?
    – S.Sharma
    Jun 17, 2014 at 10:08

3 Answers 3

4

Since Contact is a Lookup for Patient, relevant Contact id is available at Patient object itself. So something like below should work. This is for going from child to parent
SELECT Contact__c FROM Patient__c WHERE Disease__c = 'your_val'

This is for going from parent to child
SELECT Id, Name, (SELECT Contact__c FROM Patient__r) FROM Disease__c

Apply your naming conventions where it's required.
If you are facing with can not understand the relationship error better to check the correct API name by generating the WSDL (Name > Setup > Develop > API > Generate Enterprise WSDL).

9
  • I have tried something similar before, but I always get the error that it doesn't understand the relationship Patient__r.
    – user8737
    Jun 17, 2014 at 10:22
  • This is because you haven't given the correct relationship name. see my update
    – highfive
    Jun 17, 2014 at 10:29
  • So, I am now using the first query, which gets me what I want. However, I need to get the ID from the Contact Object as I will pass it to massemailmessage settargetobjectids and that only accepts ids from contacts and leads etc. is it possible to change the query somehow so that i will end up selecting the id from contact? thanks
    – user8737
    Jun 17, 2014 at 10:45
  • Since the Contact object is a lookup for Patient object it's always having Id of the relevant Contact in Patient object. So when you use Contact__c field value of Patient object it's giving the same Id field value of Contact object. You can use that list for your method
    – highfive
    Jun 17, 2014 at 10:50
  • Strange, as when my code is triggered, I get the following message in salesforce: Error:Apex trigger emailTrigger caused an unexpected exception, contact your administrator: EmailTrigger: execution of AfterUpdate caused by: System.EmailException: SendEmail failed. First exception on row 0; first error: INVALID_ID_FIELD, Only contacts, leads and users allowed for targetObjectIds.: []: Class.TriggerHandler.afterUpdateWorkflowActions: line 34, column 1
    – user8737
    Jun 17, 2014 at 10:56
0

Try this.

Select (Select Contact__c From Patient__r)
From Disease__c s 
WHERE Disease__c.Name == 'Disease X'

Note: Contact could be __c or just Contact.

Hope this helps !

2
  • Thanks, but I need to select from Contact. In this example I am selecting from Patient__r and from Disease__c. So the ID I am getting in the end will be from the contact object.
    – user8737
    Jun 17, 2014 at 10:55
  • You are querying the COntact IDs. Give it a try on either workbench or force.com IDE. You are getting the IDs of COntacts where Patients are looking up that has a master of Disease = Disease X
    – JOE
    Jun 17, 2014 at 11:00
0

I think you need to query from the Patient object, try this:

for (Patient__c pat : [ SELECT
                            Id,
                            Disease__c,
                            Disease__c.Name,
                            Contact__c
                        FROM
                            Patient__c
                        WHERE
                            Disease__c.Name = 'Some Disease'    
                        LIMIT
                            1000    ]) {

    targetObjectIds.add(pat.Contact__c);                            
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .