How to avoid constraints hitting empty (NULL) values and returning wrong values?

[kramwe]

Here’s an important concept that could have implications throughout our app (and I’m not alone).

When you have a constraint on “Do a Search For”, but the constraint returns an empty (NULL) value, “Do a Search For” then returns all values (instead of none) and that’s bad.

An example:

Do a search for role_name:first_item where event = 148682… and order_number = 1, returns A (correct)
Do a search for role_name:first_item where event = 148682… and order_number = 2, returns B (correct)
Do a search for role_name:first_item where event = 148682… and order_number = 3, returns C (correct)
Do a search for role_name:first_item where event = 148682… and order_number = 4, returns A (BAD)

There is no order_number 4. Do a Search for ignores the constraint on order_number if the value is empty/non-existant and returns the first_item for the event = A.

What’s the right way to avoid hitting constraint = empty values?

[sridharan.s]

I think what @kramwe means is that when a search filter’s parameter is null, then no filter is applied. Often, this is the desired effect. Sometimes it’s the opposite of what’s desired. In the instances where it’s the opposite (i.e., we want the search to result in null when a filter is null) what are some ways to design this query?

Thanks!

[sridharan.s]

@emmanuel, as you’ve said, do a search for filtered by a null value ignores the filtered parameter. What’s the best way to structure these queries when we want a null filter value to result in a null search value?

Thanks!

[emmanuel]

I would use a condition for this and change the data source then.