Fetch only one child record of a parent in soql

Question

I have a requirement to fetch Timecard records that are related to a Project.

Below is my query:

List <Timecard__c> timecard = [
  SELECT ID,Project__c,resource_name__c  
  FROM Timecard__c 
  Where Project__c != null];

The issue here is that, for each project, there are multiple timecards logged by a resource. Eg: Resource A has 100 Timecard records under a Project.

Now, is there anyway that i can fetch only one Timecard record related to a resource for a project? This timecard record can be any random record per resource.

Please suggest.

Answer 1

UPDATE: you cannot make aggregate function in subquery, so the 1st approach isn't for you.

You can use subquery from a Project__c record:

SELECT Id, (SELECT Id, resource_name__c FROM Timecard__r ORDER BY CreatedDate DESC LIMIT 1) FROM Project__c

UPDATE: you need GROUP BY CUBE (link to documentation GROUP BY CUBE)

If the solution above is not suitable for you, you can try to use AggregateResult and get latest Timecard__c for each Project__c and each resource_name__c. However, you can encounter problem with System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch, if you have a lot of Timecard__c records.

List<Id> ids = new List<Id>();
for (AggregateResult res : [SELECT Project__c, resource_name__c, MAX(Id) maxId FROM Timecard__c WHERE Project__c != NULL GROUP BY CUBE (Project__c, resource_name__c) ORDER BY GROUPING(Project__c), GROUPING(resource_name__c)]) {
    ids.add((Id) res.get('maxId'));
}

List <Timecard__c> timecard = [
        SELECT ID, Project__c, resource_name__c
        FROM Timecard__c
        WHERE Id IN :ids];