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

Community
  • 1
mcmc
  • 529
  • 1
  • 7
  • 19

1 Answers1

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);
Adrian Larson
  • 149,971
  • 38
  • 239
  • 420
  • see the edit please – mcmc Jun 04 '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 04 '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 04 '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 04 '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 04 '15 at 14:55