What results are matched with which filter in SOQL Filters

Question

I have before insert trigger on account and i want to set primary contact lookup(custom field on account) which match with account name.

For example, if account name with 'Tony Stark' is inserting then i have to find contact name with 'Tony Stark' and add the lookup in to primary contact.

For this problem i am using soql query with like filter.

List<Contact> lstContact = [SELECT id, Name FROM contact WHERE Name like '%acc.Name%'];

My question is how can i do it for multiple accounts if there is bulk upload?

Do i have to iterate for each and every account.name and update the lookup?

It will cost more SOQL queries.

Answer 1

This is something that Maps are good for.

In a nutshell, you would

1. Loop over the accounts being inserted to gather account names into a List or a Set
2. Perform a query for Contacts where Name IN :myAccountNamesCollection
3. Use the results of that query to populate a Map<String, Contact> where the key of the map (a String) is the name of the contact
4. Loop over the accounts being inserted again, and check the map you populated contains a key matching the account name. If it does, then you get the contact from the map, and assign your Account to it

That general gather-query-loop pattern is something you'll encounter and use frequently on the Salesforce platform (and in general, you'll want to operate on collections of records and use loops rather than working with individual records). It's effectively required to be used for us to stay within the governor limits that Salesforce imposes.

One thing to remember with Sets and Maps is that they are one of the few things in Apex that are case-sensitive. Maps and Sets also require exact matches, so using wildcards in your query isn't likely to work well.

Map<String, String> testMap = new Map<String, String>{'Mitchell' => 'good'};

system.debug(testMap.get('Mitchell')); // This prints 'good'
system.debug(testMap.get('mitchell')); // This prints null
system.debug(testMap.get('mitch')); // This prints null
system.debug(testMap.get('Mitch')); // This prints null
system.debug(testMap.get('Mitch%')); // This prints null, the get() method doesn't support wildcards

Answer 2

You create a list of values you want to look for, then use LIKE, as you expect:

String[] names = new String[] { 'Tony%','Mary%','Alice%' };
Contact[] contacts = [SELECT Name FROM Contact WHERE Name LIKE :names];