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
VKrishVKrish 

Aggrigate Resultset Help

 

I want to create a map<id, List<id>> where key is the contact id & the value is the list of custom__c's id. Actually contact is a lookup in custom__c object & 1 contact can be link to many records in custom__c.

These are the ways I tried...

Map<id,List<Custom__C>> cMap = new Map<id,List<id>>([select contact__c, id from custom__c where contact__c in: conList group by contact__c]);



list<id> idList = new List<Id>();
for(AggregateResult results : [select Contact__c, Id FROM custom__c WHERE Contact__c IN :con GROUP BY Contact__c]){
idList.add((ID)results.get('Id'));
cMap.put((ID)results.get('Contact__c'),idList);
}

But I couldnt get what I want.

Can I use AggregateResultset just to get data grouped by something & without actually using any aggeregate methods like count(), sum() etc?

I dont want to put a select query inside contact for loop to avoid limits of governors. FYI, this code is inside contact trigger.

 

 

Any ideas?

Best Answer chosen by Admin (Salesforce Developers) 
Jeremy_nJeremy_n

I don't think you want to use an aggregate query, based on your use case. Since you need to have the ID of each Custom__c record, you don't need to summarize them. Instead, I would suggest using a query on Contact with a subquery on the Custom__c related list on Contact. This would look something like:

list<Contact> cons = [select Id, (select Id from Customs__r) from Contact where Id in :conList];
map<Id, list<Id>> cMap = new map<Id, list<Id>>();

for (Contact con : cons)
{
	list<Id> newlist = new list<Id>();
	for (Custom__c cus : con.Customs__r)
	{
		newlist.add(cus.Id);
	}
	cMap.put(con.Id, newlist);
}

 

See how that works. Good luck!

Jeremy

All Answers

Jeremy_nJeremy_n

I don't think you want to use an aggregate query, based on your use case. Since you need to have the ID of each Custom__c record, you don't need to summarize them. Instead, I would suggest using a query on Contact with a subquery on the Custom__c related list on Contact. This would look something like:

list<Contact> cons = [select Id, (select Id from Customs__r) from Contact where Id in :conList];
map<Id, list<Id>> cMap = new map<Id, list<Id>>();

for (Contact con : cons)
{
	list<Id> newlist = new list<Id>();
	for (Custom__c cus : con.Customs__r)
	{
		newlist.add(cus.Id);
	}
	cMap.put(con.Id, newlist);
}

 

See how that works. Good luck!

Jeremy

This was selected as the best answer
VKrishVKrish

Thanks for your reply!

This looks like great idea!

But I hit with some problem in the 1st line.

In my case, Contact is a look up field in Custom__c object. But I dont have any lookup of Custom__c in Contact object. It is just a related list in Contact.

So system doesnot allow me to use Custom__r in select query.

It says: Save Error: Dint understand relationship 'Custom__r' in From part of query call.

How do I solve it? Should I put that query seperately & get list of Custom__c?
Jeremy_nJeremy_n

Your description matches my code, except for the relationship name, which could be thought of as the Related List Name. You can find this on the field info screen on the Contact__c field on the Custom__c object. Generally, the relationship name would default to "Customs__r" in this case, although it might be somewhat different.

 

You need to determine the relationship name in your org and substitute it for where I wrote "Customs__r".

 

Jeremy
VKrishVKrish

Yes! you are correct! how stupid am I! Even I went with default custom__r. My relation ship name differes.

Thanks a lot for your time.

I really appriciate it