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.
DRay
·8 months agoHi Laflet ,
I’m following up to see if the solution offered by TriAnthony worked for you.
If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
DRay
·8 months agoHi Laflet ,
I’m following up to see if the solution offered by TriAnthony worked for you.
If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
Tri Anthony
·9 months agoHi Laflet,
How many combinations of location_id, category_id, and client_id are there? Selecting from all combinations of the three within a single filter, rather than using three separate filters, seems impractical since the number of combinations would grow multiplicatively.
Please let me know if I’m not understanding the requirement correctly. If you have a screenshot or a quick sketch of what you’re trying to achieve, that would be very helpful.
-Tri
Luke Flett
OP9 months agoThank you for raising this point. You are correct that selecting from all possible combinations of location_id, category_id, and client_id would be impractical, resulting in hundreds of possible unique permutations.
We can clarify the requirement as follows:
Essentially, we need to load and apply these 10 predefined, complex filters (each a specific combination of the three IDs) rather than allowing the user to select from an exhaustive list of all possible combinations.
Tri Anthony
·9 months agoHi Laflet, thank you for clarifying the requirement. It sounds like you're looking for a way to bookmark a set of filters. Have you considered these paid plugins from our partners?
There is also an older, free community plugin (Bookmarks) that offers similar functionality. However, it doesn’t appear to have been tested on Linux and may not include all the features provided by the partner plugins listed above.
DRay
·9 months agoHello Laflet,
Thank you for reaching out. I see your question hasn't gotten a response yet, so I'm asking internally to try and get you an answer.