0

I need to pull out a query that fetches:

From My Team object and Package object. Package is a child of Teams

From teams I need to pull how many packages is currently in teams and how much each packages weigh in total:

SELECT SUM(Weight__c) TotalWeight, COUNT(id) numOfPackages, 
Team__r.Phone, Team__r.Name,Team__r.id FROM Package__c GROUP BY 
Team__r.Phone, Team__r.Name,Team__r.Id

But I also need to know the last package that was added to the team:

SELECT Id,  (SELECT Id, Name, CreatedDate, weight__c FROM Package__r 
ORDER BY CreatedDate DESC LIMIT 1) FROM Team__c

How, if possible, could I combine these two queries?

1

Are you using a Master-Detail relationship between Teams and Packages? If so you can utilize a Roll-up field to both sum the weight of all packages, as well as show a count of all packages. These fields would exist on the Teams object.

If you are not using a Master-Detail relationship you might consider using the Declarative Lookup Rollup Summary tool to accomplish the same thing.

Then you could use the query you already have to get the most recent package, with the two new roll-up fields in the parent part.

SELECT Id, Total_Packages__c, Packages_Sum_Weight__c, (SELECT Id, Name, CreatedDate, weight__c FROM Package__r ORDER BY CreatedDate DESC LIMIT 1) FROM Team__c

  • Unfortunately I am not. – adming32442 Jul 17 '18 at 16:51
  • @kh9876 In that case I think your best option is to use your second query remove the limit clause, and loop through all records to calculate the total weight. – dBeltowski Jul 17 '18 at 17:42
1

You can not do this in SOQL (or for that matter any query language).

The requirement need two types of information:
1) Sum of Weight__c along with other details
2) Most resent package

Both above result need different sets of data so this can not be combine into one query.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .