How can I compare datetimes in a SOQL query to return a list to a Visualforce page?

Question

I want to display a certain group of Events in Visualforce page - my query to only get Events that were created/modified after the Event happened - first tried with a custom field, but this proved unreliable to manage the field:

public class UsersEventsExt {

User U;

public UsersEventsExt(ApexPages.StandardController controller) {
  U = (User) controller.getRecord();        
}

public List<Event> getEvents() {
  return [select subject, description, activitydate, Whatid, WhoId, comments_summary__c  from Event where Ownerid = :UserInfo.getUserId() and Customfield__c = false];
}

}

I can get the events list by changing query to:

public List<Event> getEvents(datetime ActivityDatetime) {
      return [select id, subject, description, activitydate, Whatid, WhoId, comments_summary__c  from Event where Ownerid = :UserInfo.getUserId() and LastModifiedDate < :ActivityDatetime ];
    }

But then my VF page fails. Here is what worked with the first query class:

<apex:page standardController="User" extensions="UsersEventsExt2">

  <apex:pageBlock title="Meetings">
    <apex:pageBlockTable value="{!Events}" var="c">

      <apex:column headerValue="Click to Update"><apex:outputLink value="{!URLFOR($Action.Event.Edit,c.id)}" target="_blank">Update Meeting</apex:outputLink></apex:column>

       <apex:column headerValue="Subject"><apex:outputLink value="{!URLFOR($Action.Event.View,c.id)}" target="_blank">{!c.subject}</apex:outputLink></apex:column>
      <apex:column headerValue="Name" value="{!c.WhoId}"/>
       <apex:column headerValue="Date" value="{!c.ActivityDate}"/>
       <apex:column headerValue="Account" value="{!c.WhatId}"/>


    </apex:pageBlockTable>
  </apex:pageBlock>
</apex:page>

The error lies in not getting Events from the query in the class I think? Though it looks like it does return them! any help appreciated! Thanks!

Answer 1

Having consulted with my colleagues, it appears you are right to ask about this, because comparing DateTimes is not currently supported by SOQL and thus trying to do this in-query is not going to work.

Therefore, I suggest you will need to make the date comparison in code, if you put this method in your controller, it appears to work for me:

public List<Event> getEvents() {
    List<Event> results = new List<Event>();
    List<Event> allEvents = [SELECT id, subject, description, activitydate, Whatid, WhoId, comments_summary__c, ActivityDatetime, LastModifiedDate 
            FROM Event
            WHERE Ownerid = :UserInfo.getUserId()];
    for(Event thisEvent : allEvents)
    {
        if(thisEvent.LastModifiedDate < thisEvent.ActivityDatetime)
        {
            results.add(thisEvent);
        }            
    }
    return results;
}

Of course, this is a slightly heavier operation than just returning the result of the SOQL query, so - depending on the size of your org/implementation, you should keep yourself aware of the governor limits for SOQL query results (50,000 rows) and so on. This is a working solution to list the rows that you are after though.

Answer 2

How are you passing the ActivityDatetime into the Events getter? If this is basically the sum of your code, the autowired access to getEvents from the VF page won't work with a parameter in there.

If you are after all the events that have a LastModifiedDate before their ActivityDateTime, just remove the parameter from your getter like this:

public List<Event> getEvents() {
      return [select id, subject, description, activitydate, Whatid, WhoId, comments_summary__c from Event where Ownerid = :UserInfo.getUserId() and LastModifiedDate < :ActivityDatetime];
}

You wouldn't even be able to save this code/VF in the format you have presented above (without a valid getter for Events, the VF page can't be saved as it will reference a property it cannot access in the standard or extended controller).

Answer 3

You cannot pass the variable into the method through the VF page. Your method must use the

public List getEvents()

version. Make sure you have it instead as a variable in your class somewhere and set it outside of your method call:

public class UsersEventsExt {
  User U;
  public DateTime ActivityDatetime{get; set;}