function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Carter85Carter85 

Need help writing query which includes an array list as a search parameter.

I'm trying to create a contitional query of sorts.  I have a multi-select list field on my Account object, referenced below:  

Account[] pChk = [SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:acct];

 And I want to use the specific contents from each account, whatever they may be, as a search paremeter for the below query and populate a list on my VF page with the results.  Currently this is what I have, but it is incorrect as it does not seem to accept the array in the manner I'm trying as I keep getting unexpected token errors one way or the other:

String qry = 'SELECT Name, Insurer__c, Form_Number__c, Form_Title__c, Cancellable__c, Product_Group__c, State_Specific__c, In_Use__c FROM MG_Forms__c WHERE In_Use__c = TRUE AND (Form_Title__c LIKE \''+String.escapeSingleQuotes(query)+'%\') AND (Product_Group__c INCLUDES ('+ pChk +'))';
      searchResults = Database.query(qry);

 I've also tried just making the array a list, with the same lack of results, so I'm wondering if perhaps there is a simpler way to go about this, or if I may just be missing a step somewhere.  Any help would be appreciated.

 

Avidev9Avidev9
Well you can directly bind variables in SOQL query. You need not to do the string concat.
Go through this blog, that I wrote couple of months ago. This will guide you on how to use variables inside dynamic query http://blogforce9.blogspot.in/2012/09/using-variables-in-dynamic-soql.html
Carter85Carter85

Apologies, I seem to be missing the intended method within the blog post link.  I've tweaked my attempts somewhat, with this:

List<Account> pChk = [SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:pA];
      Set<String> pdcts = New Set<String>();
      for(Integer i=0; i < pChk.size(); i++){
      pdcts.add(pChk[i].Products_Available_for_Sale__c);
      }
      String qry = 'SELECT Name, Insurer__c, Form_Number__c, Form_Title__c, Cancellable__c, Product_Group__r.Name, State_Specific__c, In_Use__c FROM MG_Forms__c WHERE In_Use__c = TRUE AND (Form_Title__c LIKE \''+String.escapeSingleQuotes(query)+'%\') AND (Product_Group__r.Name IN:pdcts)';
      searchResults = Database.query(qry);

 Or this as an alternative:

Map<String, Account> pChk = new Map<String, Account>{};
      for (Account item : [SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:pA]){pChk.put(item.Products_Available_for_Sale__c, item);
      }

 However, even though 'pdcts' in the first result and pChk in the second do seem to populate the way I intend when I check to make sure they contain values I'm still missing the proper way to execute the query so that the searchResults populates properly.  I apologize, but I've not attempted this before, and I'm sure your method does work, but I was unable to adapt it to do so.  Might there just be a simple fix for my new attempts, or do you perhaps have a more detailed example that I might look over?
Carter85Carter85

Still having a really weird problem with this.  What I've gotten up to so far is:

List<Account> prods = Database.query('SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:pA');
      List<String> pdcts = New List<String>();
      for(Integer i=0; i < prods.size(); i++){
      pdcts.add(prods[i].Products_Available_for_Sale__c);
      }
      String strList = String.valueOf(pdcts);
      
      if (strList != null){
      ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Info, 'Available products for your dealership ' + strList + '.');
      ApexPages.addMessage(myMsg);
      }
      String qry = 'SELECT Name, Insurer__c, Form_Number__c, Form_Title__c, Cancellable__c, Product_Group__r.Name, State_Specific__c, In_Use__c FROM MG_Forms__c WHERE In_Use__c = TRUE AND (Form_Title__c LIKE \''+String.escapeSingleQuotes(query)+'%\') AND (Product_Group__r.Name IN(:strList))';
      searchResults = Database.query(qry);

 It returns a list of exactly what I want, however, I think since the system appends a semicolon at the end of each entry into the list it is what is preventing the search from completing properly because it'll work if there's only one entry found in the first query.  So what I'm trying to figure out now is what would be the simplest way to strip away the unnecessary character from the list and yet still keep the values separate, or am I going to just have to make a bunch of separate string objects and pull out each element individually and check them within the second query that way?
Sean TanSean Tan

If you're using a bind variable, is there a particular reason you're binding to the string.valueOf portion variable (aka the strList) rather then binding to the pdcts variable?

 

List<Account> prods = Database.query('SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:pA');
List<String> pdcts = New List<String>();
for(Integer i=0; i < prods.size(); i++){
	pdcts.add(prods[i].Products_Available_for_Sale__c);
}
String strList = String.valueOf(pdcts);

if (strList != null){
	ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Info, 'Available products for your dealership ' + strList + '.');
	ApexPages.addMessage(myMsg);
}
String qry = 'SELECT Name, Insurer__c, Form_Number__c, Form_Title__c, Cancellable__c, Product_Group__r.Name, State_Specific__c, In_Use__c FROM MG_Forms__c WHERE In_Use__c = TRUE AND (Form_Title__c LIKE \''+String.escapeSingleQuotes(query)+'%\') AND (Product_Group__r.Name IN :pdcts )';
searchResults = Database.query(qry);

 
Carter85Carter85

Unfortunately, for some reason I'm missing at the moment, when I bind to the pdcts variable the list returns empty even though it should find at least one result for each account.  I'm just failing to figure out the right way to get it to search the list I think, based on the way it seems to be arranged when I print out the info message, which currently looks like this: Available products for your dealership (Etch;Key Plus;MPP;MPP Plus;T&W - Cosmetic;Windshield).  Unfortunately the only other way I can think of to do it at the moment that will produce the right result would be to ditch the multi-select list field and create individual fields for each product available for an account and query it that way.  Granted it wouldn't be too difficult to set it up that way, since there isn't a large amount of them, just rather disorganized/messy in terms of the coding.  Unless you or anyone else happens to have a better idea.
Carter85Carter85

Never mind, fixed it, I think, with this:

List<Account> prods = Database.query('SELECT Products_Available_for_Sale__c FROM Account WHERE Name =:pA');
      List<String> pdcts = New List<String>();
        for(Integer i=0; i < prods.size(); i++){
        pdcts.add(prods[i].Products_Available_for_Sale__c);
        }
        String strList = String.valueOf(pdcts);
        List<String> l = strList.splitByCharacterTypeCamelCase();
        if (strList != null){
        ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Info, 'Available products for your dealership ' + pdcts + '.');
        ApexPages.addMessage(myMsg);
        }
      String qry = 'SELECT Name, Insurer__c, Form_Number__c, Form_Title__c, Brochures_Available__c, Laminates_Available__c, Cancellable__c, Product_Group__r.Name, State_Specific__c, In_Use__c FROM MG_Forms__c WHERE In_Use__c = TRUE AND (Form_Title__c LIKE \''+String.escapeSingleQuotes(query)+'%\') AND (Product_Group__r.Name IN :l )';
      searchResults = Database.query(qry);

 
Carter85Carter85

Or not apparently, at this moment a partial solution because when the special characters like '&' come into play that isn't read properly.  Although I suppose I could just alter the product groups themselves to only contain standard characters, unless anyone has an idea how to account for the special ones as well?