SoqlExample

/*
 * Example class demonstrating how a hypothetical <code>SoqlQuery</code>
 * class may be used to more consistently construct a dynamic SOQL query
 *
 * @version idea
 * @author  Marty Chang (Slalom Consulting)
 */
public class SoqlExample {

    /*
     * Retrieve a list of <code>Part__c</code> records based on known
     * conditions, demonstrating two ways of constructing WHERE clauses
     *
     * @return A list of Parts
     */
    public static List<Part__c> retrievePartsWithSoql() {

        // We want to set up a new query to get Part__c records

        SoqlQuery query = new SoqlQuery('Part__c');

        // Now, we're going to add all of the fields we want

        query.select(new Set<String> {
            'Id',
            'Name',
            'Opportunity__c',
            'Opportunity__r.Id',
            'Opportunity__r.Name',
            'Opportunity__r.Reason_for_Forecast_Change__c',
            'Opportunity__r.AccountId',
            'Opportunity__r.Account.Name',
            'Opportunity__r.Account.End_Customer__c',
            'Opportunity__r.SAP_End_Customer__c',
            'Product__c',
            'Product__r.Id',
            'Product__r.Name'
        });

        // We also want to select related records via a subquery, to bring
        // in R90_Demand_Forecast__c records related to each Part__c. This
        // means

        SoqlQuery subquery = new SoqlQuery('Demand_Forecast__r');

        subquery.select(new Set<String> {
            'Id',
            'Part__c',
            'Account__c',
            'R90_Opportunity_ID__c',
            'R90_Forecast_Date__c',
            'R90_Quantity__c',
            'R90_Unit_Price__c',
            'R90_Plan__c'
        });

        SoqlConditionExpression isActivePlan = new SoqlConditionExpression(
            'R90_Plan__c',
            Soql.Operator.EQUALS,
            ForecastSettingUtil.getInstance().ActivePlan__c);

        SoqlConditionExpression isAfterStartDate = new SoqlConditionExpression(
            'R90_Forecast_Date__c',
            Soql.Operator.GREATER_THAN_OR_EQUALS,
            ForecastSettingUtil.getStartDate());

        SoqlConditionExpression isBeforeEndDate = new SoqlConditionExpression(
            'R90_Forecast_Date__c',
            Soql.Operator.LESS_THAN,
            ForecastSettingUtil.getEndDate());

        SoqlConditionExpression isInSubqueryScope =
            Soql.and(new Set<SoqlConditionExpression> {
                isActivePlan, isAfterStartDate, isBeforeEndDate
            });

        subquery.where(isInSubqueryScope);

        // Now we just select the subquery like we would any regular field

        query.select(subquery);

        // Next, we want to set up our Part__c filters. We'll show a different
        // way to more efficiently construct the filters without having to
        // instantiate a new expression each time, as long as we build the
        // final expression in order

        query.where(
            new SoqlConditionExpression(
                'Product__r.Forecastable__c',
                Soql.Operator.EQUALS,
                true));

        query.andWhere(
            new SoqlConditionExpression(
                'Opportunity__r.RecordTypeId',
                Soql.Operator.EQUALS,
                OpportunityUtil.getRecordTypeId(OpportunityUtil.RECORD_TYPE_FORECASTED_OPPORTUNITY)));

        query.andWhere(
            new SoqlConditionExpression(
                'Opportunity__r.Account.RecordTypeId',
                Soql.Operator.EQUALS,
                AccountUtil.getRecordTypeId(AccountUtil.RECORD_TYPE_SAP_CUSTOMER)));

        query.andWhere(
            new SoqlConditionExpression(
                'Stage__c',
                Soql.Operator.IN,
                ForecastSettingUtil.getForecastablePartStages()));

        // Away we go with executing the query!

        return Database.query(query);
    }    // public static List<Part__c> retrievePartsWithSoql()
}    // public class SoqlExample