[SalesForce] SOQL – query a query

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.

Best Answer

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
}

Related Solutions

[SalesForce] How to query for the first matching related records

You may be able to utilize GROUP BY to do this. Something like:

SELECT
    Master__c, MAX(LastModifiedDate)
FROM
    Child__c
WHERE
    Master__c IN :masterIds
ORDER BY
    Master__c DESC, LastModifiedDate DESC
GROUP BY
    Master__c

This will only return a single record per Master__c object. The only issue you may have is that you need to utilize aggregate functions which may cause you some issues depending on what data you want.

To give an example, I ran this query in my test org. My test org has 3 accounts with BillingState set to CA:

SELECT
    BillingState
FROM 
    Account
WHERE
    BillingState = 'CA'
GROUP BY
    BillingState

When this query is run, it only returns a single record since it grouped the results together into a single record. I did not need to use an aggregate function on BillingState because it was part of the GROUP BY. If it was not part of the GROUP BY, I would need to use an aggregate function on it. However, I wouldn't be able to pull out the Id of the account, because technically it is an aggregate of all 3 accounts. There is no aggregate function to get only a single Id from it.

If you need the Ids for your records, your first approach is best in my opinion.

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Question 1:

You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.

Accounts This Year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 2

You can get the count like this

Accounts This Year

SELECT count(Id) mycount
    FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last Year

SELECT count(Id) mycount
        FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 3

You can use the same method, just combine the Where clauses from question 1

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
And Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Best Answer

Related Solutions

[SalesForce] How to query for the first matching related records

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Related Topic