+ Start a Discussion
JSpenceJSpence 

SOQL parent to child / child to parent questions

I'm struggling to get my head properly into soql queries.. I have two objects(Appointment and Quote) which lookup on Opportunity. I want some of the information in these objects which relate to an opportunity.

 

From what exampIes I have seen, this might be possible?:

SELECT something,
(SELECT somethingelse FROM child)
FROM
Parent

 

But I'm getting errors that it doesn't understand the relationship.

 

Here's a non working example of the sort of thing I'm looking for

 

SELECT
id, Scheme__c, Entity__c, Account.Fuel__c,
(SELECT id FROM Quote__c WHERE Is_Primary__c = true AND isdeleted = false),
(SELECT id FROM Appointment__c WHERE Type__c = 'Survey' AND isdeleted = false ORDER BY CreatedDate DESC limit 1),
(SELECT id FROM Appointment__c WHERE RecordType.name = 'Inspection' AND isdeleted = false ORDER BY CreatedDate DESC limit 1)
FROM Opportunity
WHERE isdeleted = false

 

any pointers would be appreciated! 

Ankit AroraAnkit Arora

When you put a inquery you need to get the child relationship name, let me explain you this using an example :

 

List<Account> accLst = [select id from account] ;

 

By this query you can get all account records.

 

List<Contact> conLst = [select id from contact] ;

 

This will return you all contact, now what if I want to put contact query as inquery in account.

 

List<Account> accLst = [select id,(select id from contacts) from account] ;

 

Here if you noticed that I have used "Contacts" instead of "Contact" as relationship name of account and contact is "Contacts"

 

How you can find child relationship name : In this case Setup > App Setup > Customize > Contacts > Fields > Click on Account Name (Lookup) > Now check Child Relationship Name.

 

Like this you can get child relationship name of any lookup field.

 

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

JSpenceJSpence

Excellent thanks, very helpful. Although I'm still getting that it doesn't understand the relationship with the 'Child Relationship Name'.

Ankit AroraAnkit Arora
SELECT
id, Scheme__c, Entity__c, Account.Fuel__c,
(SELECT id FROM Quote__c WHERE Is_Primary__c = true AND isdeleted = false),
(SELECT id FROM Appointment__c WHERE Type__c = 'Survey' AND isdeleted = false ORDER BY CreatedDate DESC limit 1),
(SELECT id FROM Appointment__c WHERE RecordType.name = 'Inspection' AND isdeleted = false ORDER BY CreatedDate DESC limit 1)
FROM Opportunity
WHERE isdeleted = false

 In above code, first of all you can not put two SOQL inquery on same object in this case "Appointment__c". Second please let me know the exact error in which relationship you are getting error.

 

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

JSpenceJSpence

I have Opportunity and Quote. Quote looks up opportunity and the 'child relationship name' = Quotes from clicking the lookup field in the object.

 

SELECT id, Scheme__c, Entity__c, Account.Fuel__c, 
(SELECT id FROM Quotes WHERE Is_Primary__c = true) 
FROM Opportunity
WHERE isdeleted = false

 

The error I'm getting is:

Didn't understand relationship 'Quotes' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Ankit AroraAnkit Arora

This query :

 

List<Opportunity> opp = [SELECT id, (SELECT id FROM Quotes) FROM Opportunity WHERE isdeleted = false] ;

 is working fine at my end. I hope you have quotes enabled on your org.

 

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

JSpenceJSpence

There's nothing that says it isn't enabled and I've stipped it down to the same as your query. Even with a different child relationship:

 

List<Opportunity> opp = [SELECT id, (SELECT id FROM Appointments) FROM Opportunity WHERE isdeleted = false];

 Still throws the 'Didn't understand relationship 'Appointments' in FROM part of query call' error.

Ankit AroraAnkit Arora

Let me know on which edition you are working on and you are running this script from eclipse or system logs?

 

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

JSpenceJSpence

Running it through system log. Custom object relationships work too right?

 

Why does the edition matter? I'm on one of the higher ones with the sandbox.