6

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;
    }

}

2
  • For the one field on the parent you need to track, you could have a secondary field, "Field_X_Changed", and a workflow rule to turn it on or off depending on what happened. I don't know how many fields you are tracking in the child records, but I doubt that you'd want to do this in bulk.
    – Jagular
    Sep 17, 2014 at 20:18
  • Not a bad idea. Although, I would like to keep it in the controller or VF page if possible. I was thinking if I used a few variables to hold the child queries that might allow me to filter like I need. Not sure how to do that though. Any ideas?
    – Nathan
    Sep 17, 2014 at 21:33

1 Answer 1

6

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;
}
4
  • CaspNZ -Where would you suggest I put that? I need it to run along with the initial query as the Dates are updated with the submit button. Can I put in the same function? Thanks for your help!
    – Nathan
    Sep 19, 2014 at 22:10
  • Hi, just added an addition to my answer. Sep 20, 2014 at 18:50
  • I tried what you recommended and am getting an error: Invalid bind expression type of SOBJECT:Attachment for Id field of SObject Case__c. I will answer my question with the controller setup that I tried. Thanks.
    – Nathan
    Sep 22, 2014 at 18:01
  • My code had a bug - the attachmentParentIds list should be of type Id not Attachment - fixed. Sorry about that. Sep 22, 2014 at 19:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .