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