0

I'm aware of this and this posts. They very well explains how to bulkify LIKE queries. But I have to query on multiple fields.

Let say I have in input a list of account names:

1. Salvator Dali
2. Claude Monet

And alredy in SF the capitalized accounts:

3. SALVATOR DALI
4. CLAUDE MONET

but also one particular case that I don't want inside the query result:

5. CLAUDE DALI

How to write a query that, given a list with 1 and 2 gives me the results 3 and 4 but not the 5?

Thanks.

EDIT

I have to query on Firstname and Lastname on Person Account objects. (in the case 1 the Firstname will be Salvator and Lastname will be Dali) and in input I will have two List: one for firstnames and one for lastnames

3
  • You don't need fuzzy search here, SOQL is case-insensitive. – Adrian Larson Jun 4 '15 at 14:22
  • Ohu.. so, what the query looks like? – mcmc Jun 4 '15 at 14:33
  • Cant you do ... "WHERE myField__c LIKE :myListToRetreive AND myField__c NOT LIKE :myListToExclude" – James Jun 4 '15 at 14:34
2

You didn't specify which other fields you want to search on, but for your simple use case, the following will work:

Set<String> names = new Set<String> { 'SALVADOR DALI', 'CLAUDE MONET' };
List<Account> matches = [SELECT Name FROM Account WHERE Name IN :names];
//Finds Salvador Dali and Claude Monet
//Ignores Claude Dali

EDIT

Okay so I understand the problem better now, you have two potential approaches, either you can find all exact matches by merging in clauses like (FirstName='Salvador' AND LastName='Dali') with an OR join, or you can query where the FirstName and LastName are in your collections and do some post-processing. I prefer the latter especially because it does not require any dynamic SOQL.

Collections Approach:

Map<String, String> firstToLast = new Map<String, String>
{
    'SALVADOR' => 'DALI',
    'CLAUDE' => 'MONET'
};
List<Account> matches = new List<Account>();
for (Account person : [
    SELECT FirstName, LastName FROM Account
    WHERE FirstName IN :firstToLast.keySet()
    AND LastName IN :firstToLast.values()
])
{
    if (firstToLast.get(person.FirstName) == person.LastName)
        matches.add(person);
}

Dynamic SOQL Approach:

Map<String, String> firstToLast; // same as above
List<String> nameClauses = new List<String>();
for (String firstName : firstToLast.keySet())
{
    String nameClause = '(FirstName=\'' + firstName + '\'' +
        ' AND LastName = \'' + firstToLast.get(firstName) + '\')'
    nameClauses.add(nameClause);
}
String namesClause = String.join(nameClauses, ' OR ');
String soql = 'SELECT FirstName, LastName FROM Account WHERE ' + namesClause;
List<Account> matches = Database.query(soql);
5
  • see the edit please – mcmc Jun 4 '15 at 14:40
  • Gotcha. I removed LIKE from your post title because it was very misleading. You do have exact matches, the problem is of a different nature. :) – Adrian Larson Jun 4 '15 at 14:42
  • nice, so you are getting all the combinations and with post-processing you're filtering only the right ones.. but.. I was googling about SOSL.. souldn't be better? – mcmc Jun 4 '15 at 14:52
  • because on scaling up properly.. in an org with A LOT of accounts.. this for loop can overtake the 10s.. isn't it? – mcmc Jun 4 '15 at 14:54
  • Depends mostly on the size of your input collections. If it's smaller, go with the collections approach, if it's larger, maybe move to dynamic soql. You have to worry about character limits though... – Adrian Larson Jun 4 '15 at 14:55

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.