Where Statement issue with SOQL

Question

I am trying to do the impossible here. I have a parent object that I am querying along with 3 of its child objects. I have filtered each of the child objects and the parent objects based on an input from the user with a date range. Everything is working great. I now need to show the parent object records if one of the fields of the parent object has been modified or if any of the child object fields have been modified. I thought this was a simple Where OR statement but there are so many limitations in SOQL that I can't figure it out. I am hoping that someone out there has an idea, preferably with examples, that can assist me in this final task. Thanks for your help!

This is what I have tried so far:

WHERE Case_Status__c = 'Active' AND 
                        ((Id IN (SELECT NotesCase__c FROM Notes__c
                                        WHERE LastModifiedDate >= :taskRec.ActivityDateTime_del__c
                                        AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c)
                          )
                          OR
                          (Id IN (SELECT ParentId FROM Attachment
                                        WHERE CreatedDate >= :taskRec.ActivityDateTime_del__c
                                        AND CreatedDate <= :taskRec1.ActivityDateTime_del__c)
                          )
                          OR
                          (LastModifiedDate >= :taskRec.ActivityDateTime_del__c 
                              AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c)
                         )

ERROR I GET: Compile Error: Entity 'Attachment' is not supported for semi join inner selects

CONTROLLER:

public class CasesandSubjectsController{
    public task taskRec{get; set;}
    public task taskRec1{get; set;}
    public CasesandSubjectsController(ApexPages.StandardController sc){
        taskRec= new task (ActivityDateTime_del__c = Datetime.now().addMonths(-1));
        taskRec1= new task (ActivityDateTime_del__c = Datetime.now());
    }
    public Case__c[] ca {
        get {
            return [
                    SELECT Id, Name, Action_Taken__c, LastModifiedDate, Case_Name__c, Case_Status__c, Sec_Case_Type__c, Sec_Summary__c, Report_StartDate__c, Report_EndDate__c, 
                            (SELECT Subject_Name__r.Id, Subject_Name__r.name, Subject_Name__r.Subject_Type__c FROM Subject_for_Cases__r), 
                            (SELECT name, Body__c, LastModifiedDate FROM Notes__r
                                    WHERE LastModifiedDate >= :taskRec.ActivityDateTime_del__c
                                    AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c
                                    ORDER BY LastModifiedDate DESC), 
                            (SELECT createddate, Id, name, ParentId FROM Attachments
                                    WHERE CreatedDate >= :taskRec.ActivityDateTime_del__c
                                    AND CreatedDate <= :taskRec1.ActivityDateTime_del__c
                                    ORDER BY CreatedDate DESC)
                    FROM Case__c
                    WHERE Case_Status__c = 'Active'                              
                    ORDER BY LastModifiedDate DESC             
                    ];
           }
    }
    public PageReference next(){
        PageReference nextpage = new PageReference('/apex/securityreport1ppv');
        return nextpage;
    }
    public PageReference next1(){
        PageReference nextpage = new PageReference('/apex/securityreport1sap');
        return nextpage;
    }
}

Got it working with. Here is the final Controller.

CONTROLLER:

public class CasesandSubjectsController{
    public task taskRec{get; set;}
    public task taskRec1{get; set;}
    Set<Id> attchmentParentIds = new Set<Id>();
    Set<Id> notesId = new Set<Id>();
    public CasesandSubjectsController(ApexPages.StandardController sc){
        taskRec= new task (ActivityDateTime_del__c = Datetime.now().addMonths(-1));
        taskRec1= new task (ActivityDateTime_del__c = Datetime.now());
    }
    public Case__c[] ca {
        get {
                Attachment[] attachments = [SELECT ParentId FROM Attachment
                                        WHERE CreatedDate >= :taskRec.ActivityDateTime_del__c
                                        AND CreatedDate <= :taskRec1.ActivityDateTime_del__c];
                Notes__c[] notes = [SELECT Id FROM Notes__c
                                        WHERE LastModifiedDate >= :taskRec.ActivityDateTime_del__c
                                        AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c];
                Case__c[] cases = [SELECT Id, Name, Action_Taken__c, LastModifiedDate, Case_Name__c, Case_Status__c, Sec_Case_Type__c, Sec_Summary__c, Report_StartDate__c, Report_EndDate__c, 
                            (SELECT Subject_Name__r.Id, Subject_Name__r.name, Subject_Name__r.Subject_Type__c FROM Subject_for_Cases__r), 
                            (SELECT name, Body__c, LastModifiedDate FROM Notes__r
                                    WHERE LastModifiedDate >= :taskRec.ActivityDateTime_del__c
                                    AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c
                                    ORDER BY LastModifiedDate DESC), 
                            (SELECT createddate, Id, name, ParentId FROM Attachments
                                    WHERE CreatedDate >= :taskRec.ActivityDateTime_del__c
                                    AND CreatedDate <= :taskRec1.ActivityDateTime_del__c
                                    ORDER BY CreatedDate DESC)
                    FROM Case__c
                        WHERE Case_Status__c = 'Active' AND
                            ((Id IN :notesId)
                                  OR
                             (Id IN :attchmentParentIds)
                                  OR
                             (LastModifiedDate >= :taskRec.ActivityDateTime_del__c 
                              AND LastModifiedDate <= :taskRec1.ActivityDateTime_del__c)
                            )
                        ORDER BY LastModifiedDate DESC ];

                return cases;
            }
    }
    public PageReference next(){
        PageReference nextpage = new PageReference('/apex/securityreport1ppv');
        return nextpage;
    }
    public PageReference next1(){
        PageReference nextpage = new PageReference('/apex/securityreport1sap');
        return nextpage;
    }

}

Answer 1

You should be able to set up that Attachment Id list in advance like so:

Set<Id> attchmentParentIds = new Set<Id>();
Attachment[] attachments = [SELECT ParentId FROM Attachment
                                        WHERE CreatedDate >= :taskRec.ActivityDateTime_del__c
                                        AND CreatedDate <= :taskRec1.ActivityDateTime_del__c];

for (Attachment a : attachments){
    attchmentParentIds.add(a.ParentId);
}

Then you'd just reference this in your main query:

OR (Id IN :attchmentParentIds)

UPDATE the get funtion would look something like this:

get {
    Attachment[] attachments = [SELECT... (select referred to above)];
    Case__c[] cases = [SELECT Id, Name, Action_Taken__c, ... (select in main question above but now referring to the attachments list in the query)];

    return cases;
}