[SalesForce] How to display Account as search results with multiple Contacts (using Visualforce)

I'm creating a visualforce page with search field for Account Name and the search result output should have the following details in a table: Account Name, Billing Country, Shipping Country, CONTACT NAME, Type, Year Created.

Now, I am able to display and search individual accounts like the one in the figure:

enter image description here

But what I need is to display the Account Names repeatedly for every Contacts it has, like the next figure:
enter image description here

My search query goes like this

string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c, (SELECT Id, Name FROM Contacts__r) FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
acc= Database.query(searchquery);

But whenever I try to display the Contacts__r field in visualforce page using

<apex:column value="{!a.Contacts__r}" /> 

I get this error:

Could not resolve field 'Contacts__r' from value
binding '{!a.Contacts__r}' in page accountsearch

What could I have been doing wrong? Anyone who can shed some light regarding this is greatly appreciated!

EDIT——————————————————–
I tried using a wrapper as @Ankita has mentioned but I can't seem to make it work. Can you please help me debug my code?

public class AccountSearchController {

Public list <account> acc {get; set;}
Public list <contact> con {get; set;}
Public String country {get; set;}
Public String contactName {get; set;}
Public Account acct{get; set;}
Public Contact cont{get; set;}

public List<aTableRow> tableRows { get; set; }

public class aTableRow {
    public Contact theContact { get; set; }
    public Account theAccount { get; set; }
    public aTableRow( Account a, Contact c) {
        theContact = c;
        theAccount = a;
    }
}


Public AccountSearchController (ApexPages.StandardController controller)
{
    acct=(Account)controller.getRecord();
    acct.OwnerId = UserInfo.getUserId();

    cont=new Contact();
    cont.OwnerID = UserInfo.getUserID();

    search();

    con = [SELECT Id, Name, AccountId FROM Contact WHERE Contact.AccountId IN :acc];

    tableRows = new List<aTableRow>();
    for(Account a: acc){
        for(Contact c: con) {
            tableRows.add(new aTableRow(a,c));
        }
    }

}

Public void search(){

    string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
    acc= Database.query(searchquery);

    string searchqueryCountry='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE BillingCountry LIKE \'%'+country+'%\' Limit 20'; 
    acc= Database.query(searchqueryCountry);

    string searchqueryContact='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Contact__r LIKE \'%'+contactName+'%\' Limit 20'; 
    acc= Database.query(searchqueryContact);

    string searchqueryType='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c FROM Account WHERE Type LIKE \'%'+acct.Type+'%\' Limit 20'; 
    acc= Database.query(searchqueryType);   



}

}

Best Answer

Account and Contact have master detail relationship. So, one account can have multiple contacts. To display the Account and its multiple contact, change your SOQL to fetch the Contact records for the given Account Name and then iterate through the Contact records.

Your query should be:

string searchquery='SELECT Id, Name, Account.BillingCountry, Account.ShippingCountry, Account.Type, Account.Year_Created__c FROM Contact WHERE Account.Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
List<Contact> = Database.query(searchquery);

VF page:

<apex:pageBlock title="My Content">
    <apex:pageBlockTable value="{!contacts}" var="contact">
        <apex:column value="{!contact.Account.Name}"/>
        <apex:column value="{!contact.Account.BillingCountry}"/>
        <apex:column value="{!contact.Account.ShippingCountry}"/>
        <apex:column value="{!contact.name}"/>
        <apex:column value="{!contact.Account.Type}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

For the scenario that if no contact is present for the Account, create a Wrapper like this

public AcctContactWrapper {
     public String acctName {get;set;}
     public String billingCountry{get;set;}
     public String contactName{get;set;}
     // include rest of the fields here

}

The query should be same

string searchquery='SELECT Id, Name, BillingCountry, ShippingCountry, Type, Year_Created__c, (SELECT Id, Name FROM Contacts) FROM Account WHERE Name LIKE \'%'+acct.Name+'%\' Limit 20'; 
acc= Database.query(searchquery);

List<AcctContactWrapper> wrappers = new List<AcctContactWrapper>();
for(Account acctObject : acc){
   if(acctObject.Contacts != null && acctObject.Contacts.size > 0){
       AcctContactWrapper wrapperObj = new AcctContactWrapper();
       wrapperObj.acctName  = acctObject.Name;
       wrapperObj.billingCountry = acctObject.BillingCountry;
       // fill for other fields
       wrappers.add(wrapperObj);
   } else {
       for(Contact c : acctObject.Contacts){
           AcctContactWrapper wrapperObj = new AcctContactWrapper();
           wrapperObj.acctName  = acctObject.Name;
           wrapperObj.billingCountry = acctObject.BillingCountry;
           wrapperObj.contactName= acctObject.Contact.Name;
           // fill for other fields
           wrappers.add(wrapperObj);
       }
   }
}

VF page:

<apex:pageBlock title="My Content">
    <apex:pageBlockTable value="{!wrappers}" var="wrapperObject">
        <apex:column value="{!wrapperObject.acctName}"/>
        <apex:column value="{!wrapperObject.billingCountry }"/>
        <apex:column value="{!wrapperObject.contactName}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

Refer Wrapper in Salesforce

Related Topic