6
public List<phonesterDat> getCompanyData() {
List<phonesterDat> var1 = new List<phonesterDat>();
for (Keyword__c keyword : [select kw__c from Keyword__c]) {
   phonesterDat var = new phonesterDat();
   String str = keyword.kw__c;
   var.setType(str);
   var.setTotal([select count() from Account where DD_Segment__c includes (:str)]);
   var1.add(var);
  }
return var1;
}

The problem in the above code are SOQL queries. Using query in for loop hits governer limits and probably is not best practice.

Here is what I am trying to do..

Trying to retrieve number of accounts which has given keyword in custom field. How can rewrite the above code?

4

Due to the way you are currently storing your keywords in a single text field I don't think your going to be able to make your current code any more efficient.

I think your first change should be to create a link object between Accounts and Keyword__c that contains a lookup to each. You should be able to use a quick script like the one above to populate this link table.

Second you will probably have to create a new interface / method to link and unlink the keywords.

Finally you can replace your code above with a query similar to this:

LIST<Keyword__c> keywordids = [select id from Keyword__c];

LIST<AggregateResult> results = [SELECT Keyword_Link__c, count(Account_Link__c) FROM Account_Keyword_Link__c WHERE Keyword_Link__c = :keywordids GROUP BY Keyword_Link__c];

UPDATE

To create a new object follow this guide: https://login.salesforce.com/help/doc/en/dev_objectcreate.htm

You then need to add two new lookup fields, one pointing to the account object and the other to your keywords object.

To actually populate this you will have to write some code that loops through your accounts and looks at which keywords are set on that account then create a record in the new object.

  • +1. INCLUDES used in the nested SOQL means that it's a multiselect picklist, so even SOSL won't help here. I think your solution is the right way (apart from more obscure things like enabling tags and tagging the account with keywords). – eyescream Nov 8 '12 at 14:12
  • can you shed some light on creating link object, more precisely how would I go about populating this fields? (I am new to sfdc development) – Bhushan Lodha Nov 9 '12 at 10:53
  • @BhushanLodha I've updated the answer with a bit more info, you should be able to get more detailed information on creating new objects in salesforce via Google. The 'link' object is nothing more than a normal object. the 'link' part just refers to its job of creating a relationship between two different objects. – Jon Hazan Nov 9 '12 at 11:25
1

Depending on how many keywords there were in your table you could do something with dynamic SOQL perhaps. This example uses the Winter13 String function join, which is pretty cool. This example naively assumes that there is at least one keyword present - so you'd need to test the validity of this assumption. Also there is a limit on the complexity/length of a single query so this won't work with hundreds of keywords.

Any thoughts?

List<String> keywordsOfInterest = new List<String>();
for (keyword__c k : [select kw__c from Keyword__c]) {
    keywordsOfInterest.add(k.kw__c);
}
// Prepare dynamic soql based on keywords
String dsoql = 'select DD_Segment__c, count(id) from Account where DD_Segment__c includes';
dsoql += String.join(keywordsOfInterest, ' or DD_Segment__c includes ');  //Ooooh! new string function
dsoql += ' group by dd_segment__c';
// Note - query will be too complex if number of keywords is large

List<AggregateResult> ars = Database.query(dsoql);

//Do the parsing of AggregateResults to get the count per keyword`enter preformatted text here
  • if there is single keyword each query, the code is no different than putting query in for loop – Bhushan Lodha Nov 9 '12 at 10:52
1
  1. Collect the keywords first
  2. Run a select from Account
  3. Manually parse the results and pray for not hitting script statements

// 1
Map<String, Integer> keywordHits = new Map<String,Integer>();
for (Keyword__c keyword : [select kw__c from Keyword__c]) {
    keywordHits.put(keyword.kw__c, 0);
}

// 2
for(Account a : [SELECT DD_Segment__c FROM Account
    WHERE DD_Segment__c INCLUDES (:keywordHits.keyset())]){
    // 3
    for(String key : a.DD_Segment__c.split(';')){
        keywordHits.put(key, ++keywordHits.get(key));
    }
}

// Final loop to display them.
for(String key : keywordHits.keySet()){
    System.debug(key + ' occurs on picklists in ' + keywordHits.get(key) + ' Account(s)');
}

Something like that should use up only 2 queries.

I'm actually surprised that INCLUDES ('a','b') on multi selects returns rows that have only 'a' and not 'b' :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.