0

I have a scenario where we need to look at records where at least one of two conditions must be met, but we also need to include where both conditions are met.

Here is the query:

SELECT Name, ID, Order_Number__c FROM Order_Line_Item__c WHERE ((Documents_Available_POD_Sent__c = null AND POD__c = TRUE) OR (Documents_Available_MTR_Sent__c = null AND MTR__c = TRUE)) ORDER BY createddate

The OR condition

WHERE ((Documents_Available_POD_Sent__c = null AND POD__c = TRUE) OR (Documents_Available_MTR_Sent__c = null AND MTR__c = TRUE))

brings back records where one of the two conditions is met.

However, we also need to incorporate where both conditions are met and return those records as well. I'm looking for suggestions in how to structure a query that provides both.

Thank you in advance!

1
  • An OR condition like "A OR B" will include results where A is true or B is true or both are true. I'm not sure I understand what your problem is with your query; you will get where both or one or other is met.
    – Phil W
    Nov 10, 2023 at 16:07

2 Answers 2

0

I'd hard code two queries: one with the OR and one with the AND.

Alternatively you could only execute the OR one and check if the AND condition is true as you loop over the OR results using Apex logic.

But generally speaking, the database is the best place to execute filtering logic as it is optimised for that. And the two query approach - if the query text is laid out well - is simpler to get right and simpler for future readers of the code to understand.

2
  • I don't think this answers the question.
    – Phil W
    Nov 10, 2023 at 16:08
  • Hi @PhilW I assumed this at least one of two conditions must be met, but we also need to include where both conditions are met meant two sets of results. But we'll see if there is clarification.
    – Keith C
    Nov 10, 2023 at 16:15
0

I'll simplify your query to be clearer. I used A, B, C and D to differentiate your criterias.

SELECT MY_FIELDS from MY_OBJECT WHERE (A AND B) OR (C AND D)

Now, assuming that this query can not retrieve what you need when all conditions are True. You can add this criteria, in that case. Your query will become:

SELECT MY_FIELDS from MY_OBJECT WHERE (A AND B) OR (C AND D) OR (A AND B AND C AND D)
3
  • 1
    The OR (A AND B AND C AND D) is unnecessary (as Phil W mentioned) because if all of those conditions are true, then we'd have (A AND B) = true and (C AND D) = true, and (true) OR (true) results in true. As a result, that additional filter does not include or exclude any additional records.
    – Derek F
    Nov 10, 2023 at 17:25
  • Totally agree, but since asker said: > brings back records where one of the two conditions is met. I've assumed that SOQL is currently working like XOR (Exclusive or), I've not done a test to confirm it. Nov 10, 2023 at 17:44
  • 1
    SOQL doesn't have an XOR operator, the OR really is acting as logical OR. If there are any records where A, B, C, and D are all true, then the results that the asker has sampled as a spot check just happened to not include them. That, or the asker's word choice is imprecise.
    – Derek F
    Nov 10, 2023 at 18:34

You must log in to answer this question.

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