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

Question

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:

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

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);   



}

}

Answer 1

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

Answer 2

You just need to replace object API of contact,

Use Contacts instead of Contacts__r in your soql

__r represents a custom relationship. There are two uses for __r. We use it when we query a custom relationship from child to parent, or from parent to child.

Answer 3

These are arranged in pageBlockSection with 4 columns. So first 4 item will be placed horizontally(here first we will place all 4 labels), then input items will be placed in next row. Refer below code -

<apex:page controller="contactController">
  <apex:form>
  <apex:pageBlock title="Search Criteria">

      <apex:pageBlockSection columns="4">
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Account Name"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Billing Country"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Contact Name"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:outputLabel value="Account Type"></apex:outputLabel>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.Name}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.BillingCountry}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.FirstName}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem>
                    <apex:inputField value="{!con.Account.Type}"/>
                </apex:pageBlockSectionItem>

      </apex:pageBlockSection>

  </apex:pageBlock>
  </apex:form>
</apex:page>

public class contactController {
  public Contact con {get;set;}

  public contactController(){
    con = new Contact();
  }
}