0

I have the Object Case and the Object Agent and I have multiple Agents created by Case. Both of them have their Ids.

Agent has a field CaseId and I want a List with the newest Agents related with a case. How can I make that query?? Now in my List I have:

Agent with Id ='1', CaseId ='40'

Agent with Id ='2', CaseId ='40'

Agent with Id = '3', CaseId ='40'

For a Case with Id ='40'

Agent with Id ='4', CaseId ='50' Agent with Id = '5', CaseId ='50'

For a Case with Id = '50'

AND I want a list with the following Agents:

Agent with Id ='2', CaseId ='40', Agent with Id = '5', CaseId ='50'

One Agent per Case, the newest Agents using the field CreatedDate

I have tried that:

List <Agent> agents = [SELECT Id, CaseId, MAX(CreatedDate) FROM Agent GROUP BY WorkItemId];

But is giving me the following error:

MALFORMED_QUERY: Field must be grouped or aggregated: Id

Is there any way??

A lot of thanks.

  • Can you use 'ORDER BY CreatedDate DESC' ? Also your query fetches from AgentWork , but you are trying to store result in in List?Agent> – Manish Anand Jan 3 at 9:20
  • When I do that It retrieves: MALFORMED_QUERY: Ordered field must be grouped or aggregated: CreatedDate – Mar Andrés López Jan 3 at 9:30
2

I don't think that using an aggregate query on Agent is the right approach here. Aggregate functions and the GROUP BY keyword are meant to aggregate data about a group of records (what is the maximum value of field X for this group of records?) rather than pull out a single record from a group.

What you're looking for here, assuming that CaseId is a lookup or master-detail relationship field, is a parent-child subquery.

The general form of that would be

[SELECT Id, (SELECT Id FROM <child relationship name here>) FROM <parent object name here>]

This helps you out because you can use things like WHERE and ORDER BY in the subquery.

If you only want the most recently created child record for a parent, that query would look like

[SELECT Id, (SELECT Id FROM <child relationship name here> ORDER BY CreatedDate DESC LIMIT 1) FROM <parent object name here>]

Subqueries in the SELECT clause will cause a List<SObject> to be embedded in the query results for the parent object.

The "child relationship name" is usually the plural of the child object's name (with "__r" at the end if the relationship field is a custom field). Finding what the "child relationship name" that you need to use can be done with some anonymous apex.

// A Case has a relationship to Account, the field is called AccountId
// What is the child relationship name?

// We can find that out by using the "describe" information
Schema.DescribeSObjectResult dsor = Schema.SObjectType.Account;

// The SObject describe information has getChildRelationships(), which gives us a list of
//   the child relationships
for(Schema.ChildRelationship childRel :dsor.getChildRelationships()){
    // getChildSObject() returns an SObject token.
    // We could get the describe information, and then use a method to get the object name
    //   as a string... but we can directly compare to <SObject name>.SObjectType
    //   (which also gives us an SObject token).
    // Similar story for childRel.getField()
    if(childRel.getChildSObject() == Case.SObjectType && childRel.getField() == Case.AccountId){
        system.debug(childRel.getRelationshipName()); // Should print "Cases"
    }
}

Once you have the child relationship name, things are pretty simple.

// Query for Accounts, and also get the most recent Case related to each of those Accounts
List<Account> myAccounts = [SELECT Id, (SELECT Id FROM Cases ORDER BY CreatedDate DESC LIMIT 1) FROM Account LIMIT 100];

// The subquery means that each Account will have a List<Case> associated with it.
// We can get the list of cases using the child relationship name like it were any other field
Account firstAccount = myAccounts[0];
List<Case> firstAccountCases = firstAccount.cases;

// The subquery can return an empty list (just like a regular query), but it will never be null.

// There are issues with retrieving subquery results if there are too many child records
//   and Salesforce needs to perform an internal call to queryMore().
// The way around that issue is to access child records via a nested loop.
// A Map<Id, List<Case>> (where the key is the account Id) can be used to store the result
//   in a useful way
Map<Id, List<Case>> accountIdToCases = new Map<Id, List<Case>>();
for(Account myAcc :myAccounts){
    accountIdToCases.put(myAcc.Id, new List<Case>());
    for(Case childCase :myAcc.cases){
        accountIdToCases.get(myAcc.Id).add(childCase);
    }
}

If you only need a list of the most recent cases, and don't care about which Account it's related to, then you could use a simple List<Case> instead of a Map<Id, List<Case>>.

You can also query for parent fields in your subquery, e.g. Account.Owner.Name

| improve this answer | |
-1

You are trying to write Aggregate query which cannot be assigned to list

AggregateResult[] lstAggregareResult = [SELECT WorkItemId, Id, CaseId, MAX(CreatedDate) FROM Agent GROUP BY WorkItemId];

and then you can iterate over aggregate list for further computation as:

for(AggregateResult objAggre: lstAggregareResult ){   

}
| improve this answer | |
  • 1
    When I do that It says MALFORMED_QUERY: Field must be grouped or aggregated: Id – Mar Andrés López Jan 3 at 9:52
  • Add WorkItemId in the query as: AggregateResult[] lstAggregareResult = [SELECT WorkItemId, Id, CaseId, MAX(CreatedDate) FROM Agent GROUP BY WorkItemId]; – Rashmi S Jan 3 at 9:57
  • Yes, I have done that but It retrieves that error – Mar Andrés López Jan 3 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.