Need help with Subquery in parent-child relationship query

filtersparent-to-childquerysoql

I have a query where I have to query contacts and their cases only if case status != "Completed" or "Pending". But in my Parent-child query, cases are getting retrieved irrespective of the filter on status.

select id,Casedeletedcontact__c,(select id,status__c from cases1__r where status__c!='Completed' or status__c!='Pending') from contact

Am I doing anything wrong here?

Best Answer

You're using the wrong operator here. You don't want OR, you want AND.

Reason being is that if status is 'Pending', it'll satisfy the != 'Completed' bit and thus get returned in your subquery (true || false = true)

When you use AND though, both parts need to be satisfied for the result to be true (and for the child row to be returned). Since the 'Pending' and 'Completed' statuses can only ever satisfy one of those two filters, rows with those statuses will not be included. (true && false = false)

Related Solutions

[SalesForce] SOQL help with subquery where clause

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')

The above should work I believe and worth trying

[SalesForce] Need help on SOQL to query all related child case records

The basic idea is to create a formula for Ultimate Parent

BLANKVALUE(Parent.Parent.Parent.Parent.ParentId,
    BLANKVALUE(Parent.Parent.Parent.ParentId,
        BLANKVALUE(Parent.Parent.ParentId,
            BLANKVALUE(Parent.ParentId,
                BLANKVALUE(ParentId, Id)
))))

Then, you can get your relevant cases as follows:

Set<Id> parentids = new Set<Id>();
for (Case newCase : trigger.new) parentIds.add(newCase.parentId);
List<Case> relevantCases = [
    SELECT Id FROM Case
    WHERE Ultimate_Parent__c
    IN (SELECT Ultimate_Parent__c FROM Case WHERE Id IN :parentids)
];

You might be able to use recalculateFormulas() to get the values without doing an inner-join-sub-select, but there is a known issue dealing with cross-object logic, so I have my doubts it would work.

Update

The recalculateFormulas method does give you the correct value, but also causes an exception if you execute it on the trigger records:

System.UnexpectedException: Unable to create/update fields: LastModifiedDate, IsDeleted, SystemModstamp, CreatedById, CreatedDate, LastModifiedById.

Best Answer

Related Solutions

[SalesForce] SOQL help with subquery where clause

[SalesForce] Need help on SOQL to query all related child case records

Related Topic