Comparing fields in two different objects in a SOQL query

Question

I am trying to create an Apex Controller that outputs a list of event records. I would only like to print the oldest from each account where the Event is a meeting. After much thought I think the best way is to create a roll up summary on the Account record that shows the oldest date of all of the events. I then create a boolean variable in the Apex Controller that compares this field (Initial_Meeting_Date__c) with ActivityDate and use this in the WHERE clause.

public with sharing class initialMeeting {
public List<Event> finalList {get;set;}
public initialMeeting() {
    List<Account> retrievedAccounts = [SELECT ID, Initial_Meeting_Date__c 
                                FROM Account 
                                WHERE Status__c = 'Active Prospect'];

    Set<String> evt = new Set<String>();
    for (Account accts : retrievedAccounts) {
        evt.add(accts.ID);
    }

    Boolean dateCompare = true;
    if(Account.Initial_Meeting_Date__c == Event.ActivityDate) {
        dateCompare = true;
    }

    finalList = new List<Event> ();
    finalList = [SELECT Id, AccountId, Subject, ActivityDate 
                        FROM Event 
                        WHERE type='Meeting' AND dateCompare='true'
                        AND AccountId IN :retrievedAccounts];
}
public List<Event> getUsersEvents() {
    return finalList;


}

}

However Salesforce is giving me an error for dateCompare in the SOQL query.

Many thanks in advance for all your help.

Answer 1

Actually, your class has some other errors:

• Account it's not a variable
• Event is not a variable
• dateCompare is always set to true

Also, as Martin pointed out, you can't use a variable from your classe as a filter, since that isn't a field on the object being queried.

What you should do is order the events on your query and show only the ones you need.

Try something like this:

public with sharing class initialMeeting {

  public List<Event> finalList {get;set;}

  public initialMeeting() {

    Map<Id, Date>() meetingDates = new Map<Id, Date();
    for(Account acc : [SELECT Id, Initial_Meeting_Date__c 
                                FROM Account 
                                WHERE Status__c = 'Active Prospect']){
        meetindDates.put(acc.Id, acc.Initial_Meeting_Date__c);
    }

    finalList = new List<Event> ();
    for(Event ev : [SELECT Id, AccountId, Subject, ActivityDate 
                        FROM Event 
                        WHERE type='Meeting'
                        AND AccountId IN :meetingDates.keySet()]{
      if(ev.ActivityDate == meetingDates.get(ev.AccountId) finalList.add(ev);
    }
  }

  public List<Event> getUsersEvents() {
    return finalList;
  }
}

I didn't test this code, so you may have to tweak a little the comparison on the map and the ActivityDate.

Other option would be to order the events on the query and just retrieve the first from each account. Something like this:

List<Event> finalList = new List<Event>();
Set<Id> filteredAccounts = new Set<Id>();
for(Event ev : [SELECT Id, AccountId, Subject, ActivityDate 
                   FROM Event 
                   WHERE type='Meeting'
                   AND AccountId IN (SELECT Id 
                                     FROM Account 
                                     WHERE Status__c = 'Active Prospect')
                   ORDER BY ActivityDate ASC]){
    if(filteredAccounts.add(ev.AccountId)) finalList.add(ev);
}

Answer 2

You can't use a variable you declared in a SOQL query. You can only query the fields of the object.

Why not just use?

finalList = [SELECT Id, AccountId, Subject, ActivityDate 
                        FROM Event 
                        WHERE type='Meeting' AND ActivityDate = :Account.Initial_Meeting_Date__c
                        AND AccountId IN :retrievedAccounts];

And remove your variable dateCompare