Query one:

 List <EmailMessage> sortedEmails = new List<EmailMessage>(); 
    sortedEmails = [SELECT Id, FromAddress, ToAddress, BCCAddress, MessageDate, Subject, HasAttachment, Incoming, TextBody, CreatedBy.Name 
            from EmailMessage where ParentId =: currentCase.Id 
            order by MessageDate DESC ];

Query two

List<CaseComment> Casecomments = new List<CaseComment>();
    Casecomments = [SELECT Id, CommentBody, CreatedDate, ParentId FROM CaseComment where ParentId =: currentCase.Id
            order by CreatedDate DESC];

The above queries are working well to show CaseComments and Emails (Case Related list) in different sections in VF page.

I would like to show them in a single section by combining Emails and comments in order by date. How can this be accomplished?


This would be a case where you'd want to use a wrapper object pattern. You'd create a new class (often an inner class) that can hold either a CaseComment or an EmailMessage and store your query results in a list of those wrapper objects. Then, conditional formatting in Visualforce picks which object to render.

You would need to implement the Comparable Apex interface on the wrapper class to enable sorting.

The pattern looks something like this example.

public class Wrapper {
    EmailMessage em;
    CaseComment c;

    public Integer compareTo(Object other) {
         // Implement comparison logic here, sorting by whichever date
         // is earliest depending on whether an EmailMessage or CaseComment is populated.
         return 0;

In your controller, you'll have a List<Wrapper> property, and you'll populate it by looping over each query:

for (CaseComment c: [SELECT Id, CommentBody, CreatedDate, ParentId 
                     FROM CaseComment 
                     WHERE ParentId =: currentCase.Id 
                     ORDER BY CreatedDate DESC]) {
    Wrapper w = new Wrapper():
    w.c = c;

// Do the same thing for the EmailMessage query, populating Wrapper.em

In your Visualforce page, you can use conditional rendering to select which set of data points to show based on whether you have an EmailMessage or a CaseComment inside each wrapper.

You can extend the pattern and make your life easier in several ways:

  • You can copy the date of each object into your wrapper, to simplify sorting code.
  • You can go further and populate a chosen set of values from each object into your wrapper directly, to simplify your Visualforce and reduce the amount of conditional rendering. So you might have a title, subject, and a date field on your wrapper object you populate directly in Apex.
  • You might add constructors to your wrapper class that take an EmailMessage or a CaseComment and populate fields appropriately.
|improve this answer|||||

What you're looking for is a parent-child query (a.k.a. a left outer join).

There is documentation on relationship queries as well as another helpful article that goes into some depth.

The basic form of the query you're looking to write is:

  Id, <Other fields, comma separated>, 
    Id, <Other fields> 
   FROM <Child relationship name>
 FROM <Parent object name>
 WHERE <Criteria>

The thing that makes this syntax special is the "inner query" (or subquery), which is basically a standalone query surrounded in parenthesis () in the SELECT clause of the outer query.

You can use ORDER BY in both the outer and inner queries.

The other thing worthy of note is the Child relationship name. For standard relationship fields, this is usually just the plural of the object name, e.g.

[SELECT Id, (SELECT Id FROM Opportunities) FROM Account]

For custom relationship fields, you are given control over what the child relationship name is when you create the field, though I do think it defaults to the plural of the object name the relationship field points to. The difference here is that you'll need to include __r at the end (e.g. My_Custom_Objects__r)

That said, I'm not sure if this works on objects that have polymorphic relationship fields like Task and Email.

|improve this answer|||||

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.