Compare two list values with IN and NOT IN SOQL query

Question

I have two queries, each populate a list of Cases, I need to create a third query that gets me the cases with their ID in the first query but exclude them if they are in the second query.

Example:

The requirement is for me to delete cases and the account associated with that case after 30 days if the account doesn't have a case that has been modified in the past 30 days. If the account has a case that has been modified in the past 30 days then exclude it and the cases from the delete process.

Date d = System.today() - 30; 

List<Case> oldCases = new List<Case>();
oldCases = [SELECT Id, Account.Name, LastModifiedDate FROM Case 
                WHERE LastModifiedDate < :d
                And Guest_Name__c Like '%Test%'];

//Add account id's to a list so we can compare them
Set<String> accountId = new Set<String>();
for(Case c : childCases){
   accountId.add(c.AccountId);
}

List<Account> testAccounts = new List<Account>();
//Get all the accounts that had cases from the above query
testAccounts = [SELECT Id, FirstName, LastName, Account.Name FROM Account 
                            WHERE Id IN :accountId];

List<Case> ineligibleCases = new List<Case>();
//Get all the cases on the account that have been modified in the past 30 days

ineligibleCases = [SELECT Id, Guest_Name__c, AccountId, LastModifiedDate FROM Case 
                         WHERE AccountId In :testAccounts
                         AND LastModifiedDate > :d];

List<Case> casesToDelete = new List<Case>();
//Get cases that don't have tickets that have been modified in the past 30 days
casesToDelete = [SELECT Id, AccountId FROM Case 
                        WHERE Id IN :oldCases
                        AND Id NOT IN :ineligibleCases];

This is where I am having trouble, the above query returns all of the cases from the first case query called oldCases, it doesn't seem to be excluding the cases from the ineligible case query. If I have 2000 cases returned from the oldCases query this query also returns 2000 cases even though the ineligibleCases query returns me 22 rows. Is this properly SOQL syntax or am I missing something?

Set<String> accountDelete = new Set<String>();
for(Case cas : casesToDelete){
    accountDelete.add(cas.AccountId);
}

List<Account> accountsToDelete = new List<Account>();
//Get accounts to delete
accountsToDelete = [SELECT Id FROM Account WHERE Id IN :accountDelete];

for(Case c: casesToDelete){
    system.debug('Cases to delete: ' + c);
}
for(Account a: accountsToDelete){
    system.debug('Accounts to delete: ' + a);
}

Answer 1

You do not need four queries total, you should be able to do it with just one, actually.

Date threshold = Date.today().addDays(-30);
List<Account> accountsToDelete = new List<Account>();
List<Case> casesToDelete = new List<Case>();

for (Account account : [
    SELECT (SELECT LastModifiedDate FROM Cases ORDER BY LastModifiedDate DESC)
    FROM Account WHERE Id IN (
        SELECT AccountId FROM Case
        WHERE LastModifiedDate < :threshold
        AND Guest_Name__c Like '%Test%'
    )
]){
    if (account.Cases[0].LastModifiedDate < threshold)
    {
        accountsToDelete.add(account);
        casesToDelete.addAll(account.Cases);
    }
}

Explanation

The first part to consolidating these queries is to pull in child records using a subquery (see Using Relationship Queries). For each Account record that you find, you can include its child Case records. The information you need here is just LastModifiedDate, and you sort it by that field in descending order so you can find the maximum value by examining the first record in the list.

The second part of the magic above is performing a semi-join (see Comparison Operators, specifically the ID field Semi-Join section). The subquery used for this join operation is exactly what you perform in your first query, you just inline it and reduce your limits consumption.

Answer 2

Your SOQL syntax is fine, but your logic is flawed. As oldCases is the list of cases last modified over 30 days ago and ineligibleCases is a list of cases modified in the last 30 days, there is never going to be any overlap. Thus casesToDelete will always be the same as oldCases.

From the logic you describe, what you actually want to do is exclude any cases that share an account with a case modified in the last 30 days.

One additional thing:

Date d = System.today() - 30;

should not be used (and may not work, would need to check the integer date representation). I recommend the unambiguous

Date d = System.today().addDays(-30);