Apex code-how to get the data of campaign Members from Subquery

Question

I am trying to display four fields on my VF page Name, Status from (Campaign Members) and Subject and Last Modified Date from (Activity History) Object.I am not able to fetch the Name and Status fields from Campaign Members.

Can anyone please tell me How to get the Name and Status fields from Campaign Members.Below is my Controller code.

public with sharing class CampaignView {
    public Campaign camp {get; set; }
    public List<MemberWrapper> lMemberWrappers {get; set;}
    public DateTime startDate {get; set;}
    public CampaignView(ApexPages.StandardController controller) {
        camp = (Campaign)controller.getRecord();
        lMemberWrappers = new List<MemberWrapper>();
        getCampaignMembers();
        startDate = null;
        for (Campaign c : [Select ID, (SELECT Id, CampaignId, Status  FROM CampaignMembers where CampaignId = :camp.Id) FROM campaign WHERE id = :camp.Id ]) {
            for (Lead ld : CampaignMembers) {
                for (ActivityHistory ah : ld.getSObjects('ActivityHistories')) {
                    lMemberWrappers.add(new MemberWrapper(ld.Name, ah.Subject, ah.LastModifiedDate, ld.CampaignMembers.get(0).Status));
                }
            }
        }
    }

    private List<Lead> CampaignMembers;
    public List<Lead> getCampaignMembers() {
        CampaignMembers = [Select Id, Name, Phone, MobilePhone, Email, LastModifiedDate, (Select id, Campaign.Name, Contact.Phone, Lead.FirstName, Lead.LastName, Lead.Name, LeadID, ContactID, Lead.Phone, Lead.Email, Lastmodifieddate, Lead.LastmodifiedDate, Status, CampaignId, Campign_ID__c, Lead.MobilePhone  From CampaignMembers where CampaignId = :camp.Id  ),
                           (Select Subject, Id, lastModifiedDate From ActivityHistories    order by LastModifiedDate DESC  LIMIT 1  )
                           From Lead  where Id IN(select LeadId from campaignMember where campaignId = :camp.Id ) ];
        return CampaignMembers;
    }
    public class MemberWrapper {
        public Object Status {get; set;}
        public String Name {get; set;}
        public String Subject {get; set;}
        public Datetime LastActivityHistory {get; set;}
        public MemberWrapper(String Name, String Subject, Datetime LastActivityHistory, Object Status  ) {
            this.Name = Name;
            this.Subject = Subject;
            this.LastActivityHistory = LastActivityHistory;
            this.Status = Status;
        }
    }
}

Answer 1

This will take you more than one query because of the ActivityHistory requirement. There are four levels of relationships in play here: Campaign -> CampaignMember -> Lead -> ActivityHistory, so you cannot perform this using just sub-queries.

The following code should get you the info you need:

Set<Id> leadIds = new Set<Id>();
List<Campaign> campaignList = [Select ID, (SELECT Id, Status, LeadId FROM CampaignMembers) FROM campaign WHERE id = :camp.Id ];

for (Campaign c : campaignList) {
    for (CampaignMember cm : c.CampaignMembers) {
        leadIds.add(cm.LeadId);
    }
}

Map<Id, Lead> leadMap = new Map<Id, Lead>([Select Id, Name, (Select Subject, LastModifiedDate from ActivityHistories Order By LastModifiedDate DESC limit 1) From Lead Where Id IN :leadIds]);
for (Campaign c : campaignList) {
    for (CampaignMember cm : c.CampaignMembers) {
        Lead ld = leadMap.get(cm.LeadId);
        if (ld.ActivityHistories.size() > 0) {
            ActivityHistory ah = ld.ActivityHistories[0];
            lMemberWrappers.add(new MemberWrapper(ld.Name, ah.Subject, ah.LastModifiedDate, cm.Status));                
        }
    }
}

I just skipped creating the MemberWrapper if there were no activities, but you could change it to just put blank values if you wish.