SOQL - using Includes to a conditional query

Question

I have the below Apex class to query for EmailMessages tied up with the Case object.

public with sharing class CaseEmailExtension {

  public ID csId {get; set;}

  public CaseEmailExtension() {}

  private final Case currentCase;

  public CaseEmailExtension(ApexPages.StandardController currentcase) {
    this.currentCase = (Case)currentcase.getRecord();
}

  public List<EmailMessage> getSortEmails(){
    return this.csId == null
        ? new List<EmailMessage>()  // handles UI preview use case
        : [SELECT Id, FromAddress, ToAddress, BCCAddress, CcAddress, MessageDate, Subject, Incoming, HtmlBody, CreatedBy.Name   
            from EmailMessage where ParentId =: this.csId
            order by MessageDate DESC ];
  }
}

I would like to extract only the EmailMessages in which the Case Contact was involved as either To, Cc, or Bcc.

I am struggling with tweaking the SOQL query to reference the above condition.

Thank you.

Answer 1

since FromAddress, ToAddress, BCCAddress, CcAddress fields can contain more then one email separated by semicolons the best would be to use LIKE operator on those fields

String csId = '5004100000281VZAAY';
String contactEmail = [SELECT Id, ContactEmail FROM Case where Id=:csId].get(0).ContactEmail;
contactEmail = '%'+contactEmail+'%';

List<EmailMessage> messages = [
SELECT Id, ParentId, FromAddress, ToAddress, BCCAddress, CcAddress, MessageDate, Subject, Incoming, HtmlBody, CreatedBy.Name   
from EmailMessage 
where ParentId =:csId AND(
    ToAddress LIKE :contactEmail OR
    BCCAddress LIKE :contactEmail OR
    CcAddress LIKE :contactEmail
)
order by MessageDate DESC];

System.debug(messages);