1

i have SOQL query which queries for some records based on a where condition.

select id, name,account.name ... <other fields> from opportunity where eventname__c='Test Event'

i also need to get the related contact details for the account in the opportunity. ie i need to add the email ids of contact who all are part of the account in the opportunity.

For each opportunity, i need to get all the contacts emailids who are associated with the account in opportunity.

I cant really figure out how to approach this. referring the documentation i can get the contact info of a account using the query

SELECT Name,
(
SELECT LastName
FROM Contacts
)
FROM Account

How can i use this along with opportunity?

Thanks

Prady
  • 10,978
  • 39
  • 124
  • 176

1 Answers1

6

The problem is that you are trying to traverse up from opportunity to its parent (account) and then back down to the children (contacts).

I think you will have to do it in two stages, e.g. roughly like:

id[] accountids = new id[]{};
for (opportunity opp : [select accountid from opportunity where eventname__c='Test Event'])
{
      accountids.add (opp.accountid);  
}
account[] acclist = [select name, (select email from contacts) from account where id in :accountIds];
naomi
  • 1,934
  • 1
  • 14
  • 29
  • Would this actually be achieved using a SoQL query? – Prady Mar 22 '12 at 16:20
  • 4
    I don't think it's possible to get what you want with just one Soql query. With SOQL you can access the parent and/or the children of the object you are querying, but you can't access the parent's other children (e.g. starting with opportunity and querying through account to contacts is not possible) – naomi Mar 22 '12 at 16:55
  • the code i posted should give you all the accounts you want, with their contacts attached. – naomi Mar 22 '12 at 17:08
  • 2
    Hi Prady, naomi is right, you can't go up and down within the one query so you have to do it in two parts. One to get your opportunity data and account ids, and then another to pull the account contact information. – Matt Lacey Mar 22 '12 at 22:08
  • thanks naomi and @laceySnr . There is a thirdparty app which accepts a SQOL query only. i was thinking of a workaround for this. Let me know if this would be the right thing to do.. I can create a new custom object, with all the fields required for this query. I can write a batch process or class which would be called on click of button to populate the data into this object. Write a simple SOQL query on this object and pass this query to thirdparty app. – Prady Mar 23 '12 at 03:47
  • That batch and custom object sounds like the easiest way to do it, though be aware of how many records this will create in the system and check what your storage usage is like before hand! – Matt Lacey Mar 23 '12 at 03:52
  • If your query is just about one event, this might be simpler: put a custom field on Account indicating whether any of its opportunities are connected to that event. This could be populated by a trigger on opportunity or perhaps even by custom rollup (using count and putting the event field in the field criteria?) Then you could just send the query 'select name, (select email from contacts) from account where hasOppWithThatEvent__c = true'. This could work even for more than one event so long as the list was shortish and unchanging (you'd need a custom field on account for each event) – naomi Mar 23 '12 at 09:40