SOQL - query a query

Question

We're in the process of converting an Access DB to Salesforce, a lot of our reports are built from querys of querys. For example, we want to produce a report that shows some fields from the parent record plus fields from 2 child objects (but only the record where a specified date field on the child is the most recent date).

Previously, we just queried out the most recent records and then just joined them to main query. Our consultants have sort of got around the issue by adding the required fields to the parent object and then setting up a trigger to copy the data from the child to the parent, however, im not convinced this is the best solution.

Would be very grateful for any suggestions as to how to deal with this in SF or whether our consultants sol is the best option.

Answer 1

It sounds like you're talking about using nested SOQL queries. Here's an example of querying a parent and two child objects in one query, using the relationship name for each related list of objects:

list<Account> accswithchildren = [select Id, Name, CreatedDate, 
    (select Id, CreatedDate from Tasks order by CreatedDate desc limit 1), 
    (select Id, Service_Date__c from Custom_Object__r order by Service_Date__c desc limit 1) 
    from Account where Id in :setofids];

You can then loop through those Accounts in Apex, and for each one, there is a list (size 0 or 1) of Tasks and Custom_Object__c:

for (Account a : accswithchildren)
{
    list<Task> theseTasks = a.Tasks;
    list<Custom_Object__c> otherobjects = a.Custom_Object__r;
    //do something with these records
}

Answer 2

Paul -

I would considering using dynamic SOQL.

Use strings to build your queries based on the results of previous ones as needed:

List<sObject__c> objects = [SELECT Id,SomeOtherField__c,CreatedDateTime FROM sObject__c WHERE ... ORDER BY CreatedDateTime];
string query = 'SELECT Id, SomeSpecialField__c FROM Other__c WHERE Id = ';
for(sObject__c object : objects){
     query = query + '\''+object.SomeOtherField__c +'\' OR ';
}
List<sobject> newRelatedObjectList = Database.query(query);

This way you can build up sets of data in otherwise unrelated lists of objects. That query was just pseudo-code but you get the picture.

Update

Since you specified you need child relationships, you can actually query the related records and than do the logic to figure out which one has the most recent child record.

List<sobject> myobjects =[SELECT Id, (SELECT Id,CreatedDateTime FROM RelationshipName__c WHERE CreatedDateTime > 'SOMEDATE' ), (SELECT Id, CreatedDateTime FROM RealtionshipName2__c WHERE CreatedDateTime > 'SOMEDATE') FROM SObject__c ...]; 

Now again, you can use dynamic SOQL to loop over these and pull only the most recent. Unfortunately, you can use the query itself to dictate the parent result (unless there is some indication via a Roll-up into the parent.

Answer 3

Paul,

Here's an example of what you're looking for. The relationships are Device__c is parent of Instance__c is parent of Channel__c is parent of Reading__c. The query gets all of the Instances and Channels for a Device, and then the most recent Reading for each, all in a single SOQL query. I arbitrarily chose Channel for the query, since it's "in the middle" of the relationships and easy to go in each direction (up through Instance to limit it to channels of a specific device, down through Readings to get the most recent reading).

    [SELECT 
        (SELECT Update_Time__c, 
            Name
         FROM ch.Readings__r
         ORDER BY Update_Time__c DESC
         LIMIT 1
        ),
        ch.Name, ch.Units__c, ch.Reading_Count__c,
        ch.Instance__r.Name
    FROM Channel__c as ch
    WHERE 
      ch.Instance__r.Device__c = :Device.Id
    ORDER BY ch.Instance__r.Name, ch.Name
    ]

I'm thinking that all of the ch. prefixes probably aren't necessary, and are left over from another version of this query that included an additional table.

Note how you can just use Child__r to refer to related records in the child table. SOQL is definitely different from SQL, but once you get the hang of it, it's easier not having to deal with setting up your JOINs.

Answer 4

If its only reports ... Creating report types or using joined reports can help.