+ Start a Discussion
Kamatchi Devi SargunanathanKamatchi Devi Sargunanathan 

Need to use map as a condition in SOQL?

Hi All,

I have a scenario like, I need to check the owner.division as a condition in my SOQL for a cusotm object query.  But for a custom object you cannot use the owner.division field in querying the value.

But, by using map, I can retreive all the user records and check the condition from there while looping the list of custom object records as follows, 

Map<id,User> ulist = new Map<id,User>([Select id,name,division from user]);

List<CustomObject__c> customList = [Select id,name, ownerid from CustomObject__c Where <my condition here>];

for(CustomObject__c c : customList)
{
       if(ulist.get(c.ownerid).division == 'xxxx')    //This is possible
       {
              //my logic goes here
        }
}

But, I want to check the condition of this division in the query itself. Becuase would like to filter out the records in the query. Is this possible?
Please help.

Thanks in advance!
 
Best Answer chosen by Kamatchi Devi Sargunanathan
SRKSRK
if you do it like this did it solce your issues

like

Map<id,User> ulist = new Map<id,User>([Select id,name,division from user where division =: 'xxxx']);

List<CustomObject__c> customList = [Select id,name, ownerid from CustomObject__c Where ownerid in: ulist.keyset()];

then just  // because the query on custom object will return only those user's record who's division is 'xxxx'


for(CustomObject__c c : customList)
{
       //my logic goes here
}

All Answers

SRKSRK
You can create a formula filed on you CustomObject__c like owner.division and then you can use the formula field in where cluase in SOQL Query

like List<CustomObject__c> customList = [Select id,name, ownerid from CustomObject__c Where <Your_Division_formualField> =: 'xxxx'];

But using formula fileds in where cluase is not a good pratice So if you don;t have large number of records in you custome object then this aaproch is fine
if you have lerger number of record in you custom object then using formula field in SOQL will hanper your Trigger and SOQL perfomace
Kamatchi Devi SargunanathanKamatchi Devi Sargunanathan
First of all, Thanks for your idea. 

Is there a way to use the map can be a condition in query?. I mean from the following map collection.

Map<id,User> ulist = new Map<id,User>([Select id,name,division from user]);

Thank you.
SRKSRK
if you do it like this did it solce your issues

like

Map<id,User> ulist = new Map<id,User>([Select id,name,division from user where division =: 'xxxx']);

List<CustomObject__c> customList = [Select id,name, ownerid from CustomObject__c Where ownerid in: ulist.keyset()];

then just  // because the query on custom object will return only those user's record who's division is 'xxxx'


for(CustomObject__c c : customList)
{
       //my logic goes here
}
This was selected as the best answer
Kamatchi Devi SargunanathanKamatchi Devi Sargunanathan
Awesome, Thanks a lot.

This works great..!!