6

I have to do a SOQL query and I don't know how to do it in a scalable way.

In a no scalable way it would look like:

List<Items> items = new List<Items>();
items.add(new Item(1,1,2));
items.add(new Item(2,2,2));
items.add(new Item(3,3,3));
items.add(new Item(1,2,3));

List<ItemInDB> itemsInDB = new List<ItemInDB>();

for(Item aItem : items){

    itemsInDB.addAll ([
        Select Id, Name 
        from ItemInDB
        where ItemInDB.property1 =: aItem.prop1
        and ItemInDB.property2 =: aItem.prop2
        and ItemInDB.property3 =: aItem.prop3 ] );
}

The problem, is that there are no way to use it and get more than the salesforce sql querys limit. Are there any way to do it in a efficient way?

4

As martin comments on one of the answers, some care is needed here to only return rows where all 3 values match. One way to do that is to add a formula field to the SObject that concatenates the 3 values in a distinct form and then query that:

List<Items> items = new List<Items>();
items.add(new Item(1,1,2));
items.add(new Item(2,2,2));
items.add(new Item(3,3,3));
items.add(new Item(1,2,3));

Set<String> keys = new Set<String>();
for (Items i : items) {
    // Same pattern here as formula field added to SObject
    keys.add(i.prop1 + ':' + i.prop2 + ':' + i.prop3);
}

List<ItemInDB> itemsInDB = [
        Select Id, Name 
        from ItemInDB
        where NewFormulaField in :keys
        ];

PS

Responding to the comment about matching varying numbers of properties, I think using like and wildcards would work:

        // Match 1st and 3rd and any value for 2nd
        keys.add(i.prop1 + ':' + '%' + ':' + i.prop3);
        ...
        where NewFormulaField like :keys
| improve this answer | |
  • And are there any way to do it if there are a dynamical property number and you don't know how many there are? may be you choose 1 in a query and 5 in another. – David Marciel Feb 22 '17 at 10:12
  • @DavidMarciel See the PS for one suggestion. – Keith C Feb 22 '17 at 11:40
  • So you suggest a formula field using all the possible data and make a key using wildcards for the items you don't care about. It solves the issue, but I am not sure about considering a best practise, anyway you have helped a lot. Thanks for your help! – David Marciel Feb 22 '17 at 12:58
  • @DavidMarciel Well "best" is always in a specific context - the idea that there is a single "best practice" is misleading. One important factor here is the number of rows involved: if the number of rows is large adding an index (possible here as the formula is deterministic) would make sense - see Force.com Formula Fields, Indexes, and Performance Gotchas. You could post another question here asking for alternatives to this approach - I'd be interested in answers to that. – Keith C Feb 22 '17 at 14:01
1

I am not quite sure what you are trying to ask here but if you are worried about querying in a FOR loop, why can't you do like this?

List<Items> items = new List<Items>();
items.add(new Item(1,1,2));
items.add(new Item(2,2,2));
items.add(new Item(3,3,3));
items.add(new Item(1,2,3));
set<prop1Type> prope1Set = new set<prop1Type>();
set<prop2Type> prope2Set = new set<prop2Type>();
set<prop3Type> prope3Set = new set<prop3Type>();
for(Item aItem : items){
    prope1Set.add(aItem.prop1);
    prope2Set.add(aItem.prop2);
    prope3Set.add(aItem.prop3);
}

List<ItemInDB> itemsInDB = new List<ItemInDB>();

itemsInDB.addAll ([Select Id, Name 
        from ItemInDB
        where ItemInDB.property1 IN prope1Set
        and ItemInDB.property2 IN prope2Set
        and ItemInDB.property3 IN prope3Set]);
| improve this answer | |
  • 2
    Note that with this query, there would be items with other combinations of properties, such as 2,1,2, 1,1,3, etc. that get returned, so it would not be exactly the same as the question. – martin Feb 22 '17 at 9:34
  • I have thinking in many ways and it is one of them, I do not use it because, as @martin has said It would request for more than the ones you are searching for. Anyway, thanks for the help. – David Marciel Feb 22 '17 at 9:46
1

This may run into total length limits, but you could build a query dynamically if you cannot change your database definition:

List<Items> items = new List<Items>();
items.add(new Item(1,1,2));
items.add(new Item(2,2,2));
items.add(new Item(3,3,3));
items.add(new Item(1,2,3));

string whereFormat = '(ItemInDB.property1 = \'\'{0}\'\' and ' +
             'ItemInDB.property2 = \'\'{1}\'\' and ItemInDb.propery3 = \'\'{2}\'\')';

List<String> whereParts = new List<String>();

for(Item it : items)
{
    whereParts.add(String.Format(whereFormat, new List<String>{it.prop1, it.prop2, it.prop3}));
}

String query = 'select ID, Name from ItemInDb where ' + String.Join(whereParts, ' or ');

List<ItemInDb> itemsInDb = Database.Query(query);
| improve this answer | |
  • Thank you for the help, I have already choosen a different as response, but it also would fix the issue. – David Marciel Feb 22 '17 at 15:02

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.