[SalesForce] Need to build the dynamic soql query in trigger

custom object: schedule_c
fields: Runway_C and date_C
Runway_c is a picklist field having track1 and track2 values.
date__c field is datetime field.

i having the the records in schedule object
e.g airline: kingfisher
flight k-101
runway: track1
date: 22-02-2014 11.00 AM

the same records like above…now i created one trigger to prevent the same
schedule to insert.i used .adderror to show the validation error while
inserting and updating new record.

trigger is working but if i having suppose million of existing records and
also want to insert or update the thousands of records,
I need to use dynamic soql query…here what i created trigger and soql query…

**trigger:** 
 trigger newrunway on Schedule__c (before insert, before update) {
 set<DateTime> Date = new set<DateTime>();
 set<id> Runway = new set<id>();

for ( Schedule__c sch : trigger.new){
  Date.add(sch.Date__c);
  Runway.add(sch.Runway__c);

}  

**string dynamicQuery = 'Select Id,date__c,Runway__c From Schedule__c 
   WHERE     Runway__c  IN :runwayids and  Date__c IN :Dateids';
   List<Schedule__c> schedule = Database.query(dynamicQuery);**


    if (schedule.size() > 0) {
        sch.addError('Flight Already Scheduled on the Runway');

    }
    }

above i created the dynamic query but it seems wrong (problem in logic of query)
and need to change…could u help me in this to make it correct

Best Answer

As @Birthus mentioned a trigger will only work for this assuming you have less than 50k records in the DB. You can try to further filter your SOQL, but beyond that Batch may be the way to go.

Assuming that you have less than 50k records, then something like below should work, but will not work should you have a large number of Schedule__c records in the database already

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

    map<string,set<DateTime>> runway2DateMap = new map<string,set<DateTime>>();

    for(Schedule__c sched : [Select Id, Date__c, Runway__c From Schedule__c Where Date__c > TODAY And Id Not In : trigger.new]){
        if(!runway2DateMap.containsKey(sched.Runway__c)){
            runway2DateMap.put(sched.Runway__c, new set<DateTime>());
        }
        runway2DateMap.get(sched.Runway__c).add(sched.Date__c);
    }

    for(Schedule__c sched : trigger.new){
        if(runway2DateMap.get(sched.Runway__c).contains(sched.Date__c)){
            sched.addError('Flight Already Scheduled on the Runway');
        }
    } 
}

Related Solutions

[SalesForce] How to make soql query dynamic

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);

[SalesForce] Force Dynamic Soql results to include fields with null value

For me too, this behavior caused a lot of trouble.

I found not much clear documentation, but my guess is that the omission of null values is an optimization strategy of salesforce. In most cases this omission has no impact, but in lots of cases it saves memory as a very limited resource of the platform.

After some research best thing I got are the answers here: Apex JSON.serialize() with null values (RELOADED) - which cleard the backgrounds but actually didn't solve my problems.

Now my workarounds for these issues are

either to use schema methods to generate a list of all fields
or generate a list of fields (to be selected) manually

In both cases I have a List<String> of field-names. Then I create the SOQL string (from the list), execute my query and receive the sparse results.

Now I still have the field-list created before. Depending on the use case I either use this list for iterations in Apex together with SObject.get() or if I need it for JSON I render the fields as a second JSON string and let the JSON-consuming logic (in my case mostly javascript) iterate over the field-list with lookups to the result-list instead of using the result-list directly.

This pattern helped me to solve all the challenges I encountered so far and my first strong displeasure with the sparse results now changed and I actually even like them. For huge results you'll probably get a more efficient solution with sparse results instead of using full populated results.

Best Answer

Related Solutions

[SalesForce] How to make soql query dynamic

[SalesForce] Force Dynamic Soql results to include fields with null value

Related Topic