Filtering Orders by Grouped Filter Criteria with Optional Nulls
Afternoon SS community,
We need a filtering mechanism in our business where users can apply filter groups to retrieve specific orders. Each filter group consists of three fields: location_id, category_id, and client_id. When a filter value is NULL, it should behave as a wildcard and match all values for that field.
The goal is for users to select one or more filter groups at a time and retrieve all matching orders — without returning duplicates, even if the same order matches multiple groups.
Currently, I can filter by individual fields, but I’m unsure how to structure things to support grouped filters like this without requiring users to select each field individually. The business requirement is to let users select entire filter groups, not individual filters.
Sample Data:
| order_id | location_id | category_id | client_id |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 2 |
| 3 | 2 | 2 | 3 |
| 4 | 2 | 3 | 4 |
| 5 | 2 | 1 | 4 |
Filter Group Examples:
Group 1
- location_id: 1
- category_id: 1
- client_id: 1
Expected Result: Order 1 only
Group 2
- location_id: 1
- category_id: 1
- client_id: NULL
Expected Result: Orders 1 and 2
Group 3
- location_id: 2
- category_id: NULL
- client_id: NULL
Expected Result: Orders 3, 4, and 5
How can I design a filtering logic that:
- Accepts multiple filter groups (with NULLs acting as wildcards)
- Returns all matching orders
- Avoids duplicate orders if they match more than one group
Any guidance or sample query structure would be greatly appreciated!
Thanks in advance.
