function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
VioletViolet 

SOQL query to get list of custom objects that have lookup relationship with opportunity

A (custom object) has a custom lookup relatiomship to Opportunity(Opportunity_Name__c). Trying to write SOQL query to get the list of custom objects but none of the below  statements are working:

select Id,(select Id from A__c) from Opportunities

 select Id, (select Id from Opportunity_Name__c) from A__c

 
Best Answer chosen by Violet
ClintLeeClintLee
You could write this query in one of two ways.  Let's assume the API name of your custom object is Custom_Object__c.
  
List<Custom_Object__c> customObjects = [select Id from Custom_Object__c where Opportunity_Name__c = 'id-of-your-opportunity'];
Alternatively, if you want to query the opportunities and all of the child Custom Objects you could do it this way.
  
List<Opportunity> opps = [select Id, (select Id from Custom_Objects__r) from Opportunity];

In the second query above notice I added the __r instead of __c.  This identifies a relationship.  To find the actual name of this relationship go to Setup > Create > Objects > Custom Object Name.  Then click on the Opportunity_Name__c field to get the details of that field.  You should see a text box called Relationship.  Replace "Custom_Objects__r" with the actual relationship name.

To access the child objects from the second query you need to go through the Opportunity, like this:
  
List<Opportunity> opps = [select Id, (select Id from Custom_Objects__r) from Opportunity];

for(Opportunity opp : opps)
{
    System.debug('Opportunity Id: ' + opp.Id);
    
    for(Custom_Object__c obj : opp.Custom_Objects__r)
    {
        System.debug('Custom Object Id: ' + obj.Id);
    }
}
Hope that helps,

Clint
 

All Answers

ClintLeeClintLee
You could write this query in one of two ways.  Let's assume the API name of your custom object is Custom_Object__c.
  
List<Custom_Object__c> customObjects = [select Id from Custom_Object__c where Opportunity_Name__c = 'id-of-your-opportunity'];
Alternatively, if you want to query the opportunities and all of the child Custom Objects you could do it this way.
  
List<Opportunity> opps = [select Id, (select Id from Custom_Objects__r) from Opportunity];

In the second query above notice I added the __r instead of __c.  This identifies a relationship.  To find the actual name of this relationship go to Setup > Create > Objects > Custom Object Name.  Then click on the Opportunity_Name__c field to get the details of that field.  You should see a text box called Relationship.  Replace "Custom_Objects__r" with the actual relationship name.

To access the child objects from the second query you need to go through the Opportunity, like this:
  
List<Opportunity> opps = [select Id, (select Id from Custom_Objects__r) from Opportunity];

for(Opportunity opp : opps)
{
    System.debug('Opportunity Id: ' + opp.Id);
    
    for(Custom_Object__c obj : opp.Custom_Objects__r)
    {
        System.debug('Custom Object Id: ' + obj.Id);
    }
}
Hope that helps,

Clint
 
This was selected as the best answer
Shyama B SShyama B S
Hi Violet,
Please try the below query. 'As__r' is the Opportunity to the custom object (A) relationship name. It is the plural of the child name (hence adding an 's' with 'A' -> 'As') with ' __r ' appended to it. 
  
select id, (select name from As__r) from opportunity
Thanks.
VioletViolet
Thank you soo much! I was missing the __r  and now that I appended __r to my custom object it works! Thank you!
ClintLeeClintLee
Great!  Glad you got it working.
VioletViolet
Would the opposite work as well? Getting the Opportunity ID from the custom object? It is not working.

select Id,(select Id from Opportunities__r) from Custom_Object_c
ClintLeeClintLee
Since the Opportunity is a parent object you would just query the lookup field to get the Id. 
List<Custom_Object__c> customObjects = [select Id, Opportunity_Name__c from Custom_Object__c];

// access the parent opportunity with dot notation
for(Custom_Object__c obj : customObjects)
{
    System.debug('Opportunity Id: ' + obj.Opportunity_Name__c);
}

 
VioletViolet
Thank you! I'll try it.
Rage NaveenRage Naveen
Pls follow this way 
List<Opportunity> opp=[SELECT Id, Name,Account.Name,Account.Industry,(select Id, Name from Areas_of_interest__r) area from Opportunity ];
system.debug('The '+opp.Areas_of_interest__r);
for(Opportunity o : opp)
{
    for(AreaOfInterest__c(Object api Name) are : o.Areas_of_interest__r (The column which are querying inside query)
    {
        system.debug('The value of opportunity id'+ o.Id);
        system.debug('The value of Area of Intrest '+ are.Id);
        system.debug('The value of Area of Intrest '+ are.Name);
    }
}