+ Start a Discussion
DavidvzlaDavidvzla 

Dumb question about SOQL

Hello guys,

I am trying to do one of the examples in the Apex triggers where a SOQL query is used and I dont understand one of the parameters that is in line 6 which is:

06        [SELECT Id,(SELECT Id FROM Opportunities) FROM Account WHERE Id IN :Trigger.New]);

I dont understand the parameter "IN : Trigger.New])" ??, 

Complete Example:
01trigger AddRelatedRecord on Account(after insert, after update) {
02    List<Opportunity> oppList = new List<Opportunity>();
03     
04    // Get the related opportunities for the accounts in this trigger
05    Map<Id,Account> acctsWithOpps = new Map<Id,Account>(
06        [SELECT Id,(SELECT Id FROM Opportunities) FROM Account WHERE Id IN :Trigger.New]);
07     
08    // Add an opportunity for each account if it doesn't already have one.
09    // Iterate through each account.
10    for(Account a : Trigger.New) {
11        System.debug('acctsWithOpps.get(a.Id).Opportunities.size()=' + acctsWithOpps.get(a.Id).Opportunities.size());
12        // Check if the account already has a related opportunity.
13        if (acctsWithOpps.get(a.Id).Opportunities.size() == 0) {
14            // If it doesn't, add a default opportunity
15            oppList.add(new Opportunity(Name=a.Name + ' Opportunity',
16                                       StageName='Prospecting',
17                                       CloseDate=System.today().addMonths(1),
18                                       AccountId=a.Id));
19        }          
20    }
21 
22    if (oppList.size() > 0) {
23        insert oppList;
24    }
25}



I would really appreciate if anybody can help me. Thanks.
Best Answer chosen by Davidvzla
sfdcMonkey.comsfdcMonkey.com
yes it's say that query record from where id IN new or update record ID
User-added image

:)

All Answers

Jason FlippenJason Flippen
Hi Davidzla,

Is your Trigger failing or are you just wondering what the "IN" statement means?  The "IN" statement is basically a way of including a list of values to your WHERE clause with an implied "OR" clause.  In other words, if you were performing a query like this...

SELECT Id, Name FROM Account WHERE Id = "12345" OR Id = "67890";

You could replace that with this...

SELECT Id, Name FROM Account WHERE Id IN ("12345", "67890");

Both would produce the same result.

I hope this helps.

Jason
sfdcMonkey.comsfdcMonkey.com
hi Davidvzla 
this is very basic query (use inner query)
SELECT Id,(SELECT Id FROM Opportunities) FROM Account 

here two queries in one SOQL statement. One to pull the general account data (ID), and one to pull the opportunity data (id) related list.

WHERE Id IN :Trigger.New

its mean query those record where account ID IN : Trigger.new (trigger.new - is collection of records with new values due to DML Activity )
(:) this is bind variable use for assess variable value in query.

so it simple mean get account id , related opportunity id where account Id IN trigger.New (current record in context )
i hop it helps you
mark it best answer if it helps you so it make proper solution for others
Thanks
if any issue with it you can ask here :)
DavidvzlaDavidvzla
Thanks Jason, It is not giving me an error, is just that I was wondering what did "IN" meant. So, it gives a specific value to the WHERE clause right?. In the above example it will be specifyng only the new AccountId? 

I appreciate your help Jason
DavidvzlaDavidvzla
So, is it saying to get the new or updated records ?. thanks for your help, appreciate it.
Jayamaruthyraman JaganathJayamaruthyraman Jaganath
Hi,

Trigger.New 
-- Returns a list of the new versions of the sObject records. This sObject list is only available in insert, update, and undelete triggers, and the records can only be modified in before triggers.

When used in a SOQL query, the system converts this into a list of Ids.The following is from the Salesforce site.

User-added image
Hope this helps!
sfdcMonkey.comsfdcMonkey.com
yes it's say that query record from where id IN new or update record ID
User-added image

:)
This was selected as the best answer
Jason FlippenJason Flippen
Hi Davidvzla,

You are correct.  The "IN :Trigger.New" is specifying the inserted/updated Account Id(s).

Jason
Lakshmi Priya 17Lakshmi Priya 17
Hello,

Can someone please explain me the meaning of "acctsWithOpps.get(a.Id).Opportunities.size()"

Thanks in advance!
Regards,
Priya.
Jayamaruthyraman JaganathJayamaruthyraman Jaganath
acctsWithOpps - a variable of type Map.(as you can see from the first select)
get - An instance method that returns the value stored for key passed in.
a.id - is the key for which the developer is requesting the value stored in the Map-type variable.
acctsWithOpps.get(a.Id) - Is the returned value whcih is in this case an Account object.
Opportunities - List of Opportunities associted with the Account whose ID is the key for the Map object (acctsWithOpps)
size - A method that returns the number of elements in the list

In sum, acctsWithOpps.get(a.Id).Opportunities.size() returns the number of accounts associated with the account represented by a

Hope this helps!
Lakshmi Priya 17Lakshmi Priya 17
Perfect explanation!
Thanks a lot!! @Jayamaruthy
deepthi reddy 24deepthi reddy 24
For , acctsWithOpps.get(a.Id).Opportunities.size()
The acctsWithOpps.get(a.Id) itself returns value of the key which is a.ID, then why cant we use acctsWithOpps.get(a.Id).size() ?
Can you please explain? @Jayamaruthyraman Jaganath.
 
Jayamaruthyraman JaganathJayamaruthyraman Jaganath
You can try the following code on the developer console. You will get an error.

acctsWithOpps.get(a.Id).Opportunities.size() - returns the number of opportunity records on a single account record.
acctsWithOpps.get(a.Id).size() - should ideally retrun 1 but an error is raised as size call is made on a single account returned by the get call.

Map<Id,Account> acctsWithOpps = new Map<Id,Account>(
  [SELECT Id,(SELECT Id FROM Opportunities) FROM Account]);
 for(Account a : acctsWithOpps.values()) {
       System.debug('acctsWithOpps.get(a.Id).Opportunities.size()=' + acctsWithOpps.get(a.Id).Opportunities.size());
     System.debug('acctsWithOpps.get(a.Id).size()=' + acctsWithOpps.get(a.Id).size());
 }