Apex Code Development You need to sign in to do that
Don't have an account?
Cypher Complex SOQL query help
I have a SOQL query that I need to create that has to traverse 4 obejects with various relationships:
Quote is a child of Opportunity
Account is a lookup on Opportunity
Custom Account Team is a child of Account
I am starting on Quote - I need to find the sum of a value Y on all quotes where:
QuoteFlg is TRUE AND
Quote ProductLine <> 'Big Products' AND
Parent Opportunity Status = 'Won" AND
Parent Opportunity Type = 'Sale' AND
Parent Opportunity WinDate = THIS_YEAR AND
Parent Opportunity Account IS IN (Select Accounts FROM Custom Account Team at where at.Primary_flg = true and at.userId = UserID on the quote (Actually a userid from an apex page).
In SQL, I would do this:
SELECT SUM(Y)
FROM Quote q
INNER JOIN Opportuntiy o on o.Id = q.Opportunity_Id
WHERE q.QuoteFlg='Y'
AND q.ProductLine <> 'Big Products'
AND o.Status = 'Won'
AND o.Type = 'Sale'
AND o.WinDate Between [beginning of year] and [end of year]
AND o.AccountId IN
(SELECT at.AccountId FROM Account_Team at
WHERE at.Primary_Flg = 'Y'
AND at.UserId := variable)
Can someone tell me how to do this in SOQL? I am tired of all of my gesitculations: I have used relationships, and more, to no avail.
Hello @Cypher,
Nice meeting you!
You'll need to split the soql, cause the IN clause needs a Set<String>. So first you'll get the set of Account Ids.
List<Account_Team__c> accTeamList = [SELECT Account__r.Id FROM Account_Team__c WHERE Primary_Flg__c='Y' AND UserId := variable]); Set<String> accSet = new Set<String>(); for (Account_Team__c ac: accTeamList) { accSet.add(ac.Account__r.Id); }Then you can do the Quote extraction:
There is no INNER operator in soql. See here: http://wiki.developerforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
Also, for the custom fields use the full api name, the one with '__c' at the end. When referencing fields from a custom lookup use '__r', like Object__r.Field__c. For the standard lookup fields(like Account on Opportunity) use Id when referencing the field - AccountId. If you need a field from it use just Account.Name.
THIS_YEAR exists as a literal. Check here: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_dateformats.htm
You'll also need to check for null values for lists.
A fast way to test your soqls is through a small application called force.com explorer. Link: http://wiki.developerforce.com/page/ForceExplorer
Have a nice weekend,
Adrian
All Answers
Hello @Cypher,
Nice meeting you!
You'll need to split the soql, cause the IN clause needs a Set<String>. So first you'll get the set of Account Ids.
List<Account_Team__c> accTeamList = [SELECT Account__r.Id FROM Account_Team__c WHERE Primary_Flg__c='Y' AND UserId := variable]); Set<String> accSet = new Set<String>(); for (Account_Team__c ac: accTeamList) { accSet.add(ac.Account__r.Id); }Then you can do the Quote extraction:
There is no INNER operator in soql. See here: http://wiki.developerforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
Also, for the custom fields use the full api name, the one with '__c' at the end. When referencing fields from a custom lookup use '__r', like Object__r.Field__c. For the standard lookup fields(like Account on Opportunity) use Id when referencing the field - AccountId. If you need a field from it use just Account.Name.
THIS_YEAR exists as a literal. Check here: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_dateformats.htm
You'll also need to check for null values for lists.
A fast way to test your soqls is through a small application called force.com explorer. Link: http://wiki.developerforce.com/page/ForceExplorer
Have a nice weekend,
Adrian
Adrian -
Thank you SO much. I've altered the code and it works! Seriously, thanks so much for the help.