0

I wanted to query an object with two parameters which are stored in a map.

Currently the code is:

  newQuotes = new list<Quote__c>();
        for (WrapperClassEx wrap : WrapperList){
            newQuotes.add(new Quote__c(
                consumption__c=decimal.valueOf(wrap.AQQ),
                Day_rate__c=decimal.valueOf(wrap.dayrate),
                Standing_Charge__c=decimal.valueOf(wrap.standing), 
                Supplier__c=wrap.supplier,
                All_Products__c=wrap.elecObj.id
                ));
            meterIdsToInsert.put(wrap.elecObj.id,wrap.supplier);
            }
        list<Quote__c> oldQuotes = new list<Quote__c>();

            oldQuotes = [select id from quote__c where (all_products__c  = :meterIdsToInsert.keySet()) and (supplier__c = : meterIdsToInsert.values())];

The trouble is, that it will return results for each all_products_c Id and each supplier_c.

Perhaps I can best illustrate by example. My data:

-----------------------
|  id   |  string
--------------------
|  1    |     a
|  2    |     a
|  3    |     b

My map:

-----------------------
|  id   |  string
--------------------
|  1    |     b
|  2    |     b
|  3    |     a

Results should be empty. However, it comes down as

-----------------------
|  id   |  string
--------------------
|  1    |     a
|  2    |     a
|  3    |     b

2 Answers 2

2

Solution to your question could be a dynamic query. So instead of making a query inside a for loop (this is actually not a good practice because it will take more time and you can easily hit SOQL query limit, what is more in each iteration you're currently quering for the same thing) you could just add in each iteration a pair of conditions. So my suggestion is to do it like this:

if (meterIdsToInsert.isEmpty()) {
    // Do something when map is empty
}

String queryString = 'SELECT Id FROM Quote__c ';
String whereCondition;

for (Id meters : meterIdsToInsert.keySet()){
    if (whereCondition == null) {
        whereCondition = 'WHERE ';
    } else {
        whereCondition += ' OR ';
    }
    whereCondition += '(all_products__c  = \'' + meters + '\' AND supplier__c = \'' + meterIdsToInsert.get(meters) + '\')';
}

oldQuotes = Database.query(queryString + whereCondition);

So this should produce a query String like this (for your example map)

SELECT Id FROM Quote__c
WHERE (all_products__c='1' AND supplier__c='a')
    OR (all_products__c='2' AND supplier__c='a')
    OR (all_products__c='3' AND supplier__c='b')
4
  • So this will create query like this: select id form Quote__c where all_products__c=id1 and supplier__c=string1 OR all_products__c=id2 and supplier__c=string2 OR all_products__c=id3 and supplier__c=string3 ?
    – dzh
    Feb 26, 2014 at 11:56
  • dzh - almost yes. You skipped brackets, so it will look like this: SELECT Id form Quote__c WHERE (all_products__c=id1 and supplier__c=string1) OR (all_products__c=id2 and supplier__c=string2) OR (all_products__c=id3 and supplier__c=string3). I should have written this in my answer. I will update it to make it clearer.
    – neff
    Feb 26, 2014 at 11:58
  • Just tested this query in console and it seems to work! What are the limits for this though? I suppose it would be a good practice to use escapeSingleQuotes() on all strings to prevent SOQL injection.
    – dzh
    Feb 26, 2014 at 12:06
  • @dzh, you're absolutely right: escapeSingleQuotes method is recommended (there's even a note in documentation that you probably have already seen: salesforce.com/us/developer/docs/apexcode/Content/…). Regarding the limits - the same limits apply as for inline SOQL. In dynamic SOQL you however can't bind variable fields (but you still can bind variables)
    – neff
    Feb 26, 2014 at 12:16
0

First of all, Don't ever use this syntax:

for (id meters : meterIdsToInsert.keySet()){
            oldQuotes = [select id from quote__c where (all_products__c  = :meterIdsToInsert.keySet()) and (supplier__c = : meterIdsToInsert.values())];
        }

One of main goal to each developer is to avoid using queries in loops.

And about your code - it actually work? because as I can see you use Set and List as variable. You should use IN :

ldQuotes = [SELECT id 
            FROM quote__c 
            WHERE (all_products__c  IN :meterIdsToInsert.keySet()) 
                   and (supplier__c IN : meterIdsToInsert.values())];
2
  • I think this will return the same result. Feb 26, 2014 at 11:41
  • Indeed, same result. @Birthus, thanks for the tip. Was aware of it though.
    – dzh
    Feb 26, 2014 at 11:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .