0

I have this code:

List<User> lUsers = [select federationIdentifier, email, firstName, lastName, ContactId, isActive from User where isActive=True and FederationIdentifier != ''];
List<Account> lAccounts = new List<Account>();


for (User U:lUsers)
{
    Account uAccount = [select Id, PersonContactId, net_id__pc, personemail, firstName, LastName from account where PersonContactId = :U.ContactId].get(0);
    uAccount.net_id__pc = U.federationIdentifier;
    lAccounts.add(uAccount);  
}

system.debug(lAccounts);

But I am getting an error:

System.LimitException: Too many SOQL queries:101.

Is there any other way to write this code and avoid this problem?

0

You need to NEVER have a query inside of a loop.

//Get set of Ids so that you can query in bulk
Set<Id> contactIds = new Set<Id>();
for (User usr: lUsers)
{
  contactIds.add(usr.ContactId);
}

//Place info into a map
Map<Id, Account> personToAccMap = new Map<Id, Account>();
for (Account acc: [SELECT PersonContactId, net_Id__pc, PersonEmail, FIrstName, LastName FROM Account WHERE PersonContactId IN :contactIds])
{
  personToAccMap.put(acc.PersonContactId, acc);
}

for (User U:lUsers)
{
    //You can now grab without a query
    Account uAccount = personToAccMap.get(U.ContactId);
    uAccount.net_id__pc = U.federationIdentifier;
    lAccounts.add(uAccount);  
}

system.debug(lAccounts);
| improve this answer | |
  • This is working for me... but I have a doubt. At the line: for (Account acc: [SELECT PersonContactId, net_Id__pc, PersonEmail, FIrstName, LastName FROM Account WHERE PersonContactId IN :contactIds]) isn't the select within the loop? Thanks – Jose Cabrera Zuniga Sep 12 '16 at 21:53
  • Nope. That is iterating over the records returne and so is considered not looping over even though it builds the loop. – dphil Sep 12 '16 at 22:18
  • Iterating over the values in a loop is correct, while querying values in the loop is what causes the problem. – sfdcfox Sep 13 '16 at 9:13
2

You can use a Map. A typical example would look something like:

Set<Id> contactIds = new Set<Id>();
for (User lUser : lUsers) contactIds.add(lUser.ContactId);
contactIds.remove(null);

Map<Id, Account> contactIdToAccount = new Map<Id, Account>();
for (Account account : [SELECT PersonContactId FROM Account])
    if (!contactIdToAccount.containsKey(account.PersonContactId))
        contactIdToAccount.put(account.PersonContactId, account);

for (User lUser : lUsers)
{
    Account firstPersonAccount = contactIdToAccount.get(lUser.ContactId);
    // do stuff
}

However, note that there can be multiple matches.* It probably makes more sense to update all of them, and also use a separate Map to de-duplicate.

Set<Id> contactIds = new Set<Id>();
for (User lUser : lUsers) contactIds.add(lUser.ContactId);
contactIds.remove(null);

Map<Id, List<Account>> contactIdToAccounts = new Map<Id, List<Account>>();
for (Account account : [SELECT PersonContactId FROM Account])
{
    if (!contactIdToAccount.containsKey(account.PersonContactId))
        contactIdToAccount.put(account.PersonContactId, new List<Account>());
    contactIdToAccount.get(account.PersonContactId).add(account);
}

Map<Id, Account> uniqueAccounts = new Map<Id, Account>();
for (User lUser : lUsers)
{
    List<Account> personAccounts = contactIdToAccount.get(lUser.ContactId);
    if (!personAccounts == null)
    {
        for (Account personAccount : personAccounts)
        {
            // do stuff
            uniqueAccounts.put(personAccount.Id, personAccount);
        }
    }
}

* The relationship is set up as many to one. I am not certain if you can actually associate multiple Person Accounts to the same Contact.

| improve this answer | |

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.