Too many SOQL queries: 101 in apex class. How to count results?

Question

List<tempcon1> listoftemp;
CheckContactController chk = new CheckContactController();
public List<tempcon1> getconValues() {
    listoftemp = new List<tempcon1>();
    for (Temporary_contact__c temporary: [
        SELECT
            Id,
            Name,
            First_Name__c,
            Last_Name__c,
            Email__c,
            Contact_list__c 
        FROM Temporary_contact__c 
        ORDER BY createddate desc
    ]) {
        listoftemp.add(new tempcon1(temporary));
        condetails = chk.Contactdetails(temporary.Xmldata__c);
        contactlist = [
            SELECT
                Id,
                FirstName,
                LastName,
                Name,
                Email,
                Phone,
                (
                    SELECT First_Name__c, Last_Name__c, Contact_Mail_id__c 
                    FROM Contact_Mail_id__r 
                    WHERE Last_Name__c = :condetails.lastname
                    OR Contact_Mail_id__c = :condetails.email
                ) 
                FROM Contact 
                WHERE lastname = :condetails.lastname
                OR (email != '' AND email = :condetails.email)
            ]; //got an error here
        temporary.Contact_list__c = string.ValueOf(contactlist.size());
    }
    return listoftemp;
}

In this for the first time the loop enters,say suppose the contactlist Query consists of 5 records I'm passing them into temporary.Contact_list__c & so on....So it is displaying like wise for an individual item.

Since I got an error as Too many SOQL queries: 101.I have changed my method to

Set<String> conlstname = new Set<String>();
Set<String> conlstemail = new Set<String>();
public List<tempcon1> getconValues() {
    listoftemp = new List<tempcon1>();
    for (Temporary_contact__c temporary: [
        SELECT
            Id,
            Name,
            First_Name__c,
            Last_Name__c,
            Email__c,
            Contact_list__c 
        FROM Temporary_contact__c 
        ORDER BY CreatedDate DESC
    ]) {
        listoftemp.add(new tempcon1(temporary));
        condetails = chk.Contactdetails(temporary.Xmldata__c);
        conlstemail.add(condetails.email);
        conlstname.add(condetails.lastname);
    }
    contactlist = [
        SELECT
            Id,
            Firstname,
            Lastname,
            Name,
            Email,
            Phone,
            (
                SELECT
                    First_Name__c,
                    Last_Name__c,
                    Contact_Mail_id__c 
                FROM Contact_Mail_id__r
                WHERE Last_Name__c IN :conlstname
                OR Contact_Mail_id__c IN :conlstemail
            ) 
        FROM Contact 
        WHERE LastName In: conlstname
        OR (email != '' AND email IN: conlstemail)
    ];
    system.debug('contactlist contactlist ' + contactlist);
    return listoftemp;
}

Here how should I pass the size for an Individual Item. I know this is not the correct way.Any ideas on this please

Answer 1

You need to use SOQL Aggregate functions. In this example i am using the COUNT() function.

Preamble
In this small example we are counting all contacts depending on the last name:

AggregateResult[] contactListAggregated = [Select count(id), LastName
                                           From Contact
                                           Group By LastName ];

for (AggregateResult entry : contactListAggregated){
    System.debug('#### Number of contacts: ' + entry.get('LastName') + ' - ' + entry.get('expr0'));
}

The result will be like this:

#### Number of contacts: Johnson - 25
#### Number of contacts: Doe - 12
#### Number of contacts: Davis - 156

You can try this code in your developer console.

Now my example:

// First we must read the data from your temp contact object
List<Temporary_contact__c> temporaryList = [Select id,Name,First_Name__c,Last_Name__c,Email__c,Contact_list__c 
                                            From Temporary_contact__c
                                            Order By createddate desc ];

// Now iterating this list with temp contacts an saving last names and emails
for(Temporary_contact__c temporary : temporaryList)
{     
    listoftemp.add(new tempcon1(temporary));
    condetails = chk.Contactdetails(temporary.Xmldata__c);
    conlstemail.add(condetails.email);
    conlstname.add(condetails.lastname); 
}

// Now we will use aggregate function count to get the number or related contacts
AggregateResult[] contactlistAggregated = [Select count(id), LastName
                                           From Contact
                                           Where LastName IN :conlstname
                                           Or (email != null and email IN :conlstemail)
                                           Group By LastName ];

// Iterating temp list again and compare the last names
for(Temporary_contact__c temporary : temporaryList)
{  
    for (AggregateResult entry : contactlistAggregated){

        if(entry.get('LastName') == temporary.Last_Name__c)
        {
            // Here we are saving the number of related contacts to the temp list
            temporary.Contact_list__c = string.ValueOf(entry.get('expr0'));
        }
    }
}