3
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

4

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'));
        }
    }
}
| improve this answer | |
  • 1
    Just a side note, one thing to keep in mind when using an aggregate function in salesforce, they don't count as 1 to the amount of query rows returned in one context limit in salesforce (which is 50k), they count as the amount of records aggregated, See the Note in the Documentation: salesforce.com/us/developer/docs/apexcode/Content/… – pjcarly Apr 9 '13 at 8:57
  • @PJC Agree! But here all is based on the temporaryList size. Because it is a temp list is don't think it will have lots of records. But you are right, he need to add a condition to the WHERE clause to reduce the amount of records. – Sergej Utko Apr 9 '13 at 9:02
  • @mast0r Great Work...AggregateResult[] contactlistAggregated = [Select count(id), LastName From Contact Where LastName IN:conlstname Or (email != null and email IN :conlstemail) Group By LastName ]; In this Query I need to get the records of another object also(which is a lookup of Contact).Since Aggregate Function doesnot support the relationships how should I restrict it – Eagerin Sf Apr 9 '13 at 9:56

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.