[SalesForce] How to make soql query dynamic

trigger runwaynew on Schedule__c (before insert, before update) {

    Map<string,List<Schedule__c>> ScheduleMap = new Map<string,List<schedule__c>>();
    set<Date> DateSet = new set<date>();
    set<id> RunwaySet = new set<id>();

    for ( Schedule__c sch : trigger.new) {
        dateset.add(sch.Date__c);
        runwaySet.add(sch.Runway__c);
    }

    for(Schedule__c sch : [
            Select Id,date__c,Runway__c
            From Schedule__c
            WHERE  Runway__c IN:runwaySet and Date__c IN:DateSet
            ]) {
        // Combo the date + Runway
        string key = sch.runway__c+Sch.Date__c;
        if(ScheduleMap.get(key)==null)
            ScheduleMap.put(key,new List<schedule__c>());

        ScheduleMap.get(key).add(sch);
    }

    for ( Schedule__c sch : trigger.new) {
        if (sch.Runway__c != NULL && Sch.Date__c!=null) {
            string key = sch.runway__c+Sch.Date__c;

            if (ScheduleMap.get(key).size() > 0) {
                sch.addError('Flight Already Scheduled on the Runway');
            }
        }
    }
}

the above trigger is used for validation error. now, i having runway field contains track 1 and track 2 (picklist field) and date field contains datetime. now i will trigger for new record… if it contains same value as per existing saved record it will give error.

now, the main issue is i want to make dynamic query which will retrieve all records and the thing with old query is its retrieving all unwanted records. pls suggest to make the query dynamic and correct way…

Best Answer

Here's a Salesforce doc on Dymanic SOQL:

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_soql.htm

Here's the idea: First, write you're SOQL query as a string. Start with something simple.

string queryString = 'SELECT id,Name,Account.Name FROM Contact LIMIT 3';

To get the result, use the database.query method. Note that the method returns a list<sObject>.

list<sObject> sContactList = database.query(queryString);

// or cast to what you want to return 

list<Contact> contactList = (list<Contact>)database.query(queryString);

Now, let's do a more complicated SOQL query:

 map<id,Contact> contactMap = new map<id,Contact>(contactList);

 // using standard soql
 list<Contact> theSameContactList = [SELECT id,Name,Account.Name
                                     FROM Contact
                                     WHERE id IN :contactMap.keySet()];

 // but this doesn't work so straitforwardlike in dynamic SOQL
 string queryThatDoesntWork = 'SELECT id,Name,Account.Name FROM Contact WHERE id IN:contactMap.keySet()';

 // queryThatDoesntWork doesn't work because you are reference a method of an object.
 // In dynamic SOQL, you can only reference an object -- but not any of its methods

 set<id> contactKeySet = contactMap.keySet();
 string queryStringThatDOESWork = 'SELECT id,Name,Account.Name FROM Contact WHERE id IN:contactKeySet';

 list<Contact> theSameContactList = (list<Contact>)database.query(queryStringThatDOESWork);
Related Topic