Question

Querying nulls logic

Forum|Forum|3 years ago
March 15, 2023
7 replies
0 views

I was making a query today in which I wanted to find entries based on a particular field and if that field happens to be null, I want all the entries that have that field null. I ended up with 2 filters, one that looks for entries when the value is not null, and one that looks for entries when the value is null. It works and all, but the thing is there's a parameter called ignoreFiltersWithEmptyValues that is usually true by default. But you can make it false and now instead of ignoring those filters with empty values, it gives an error for each filter with empty values. My question is why does this parameter even exist if all turning it off does is cause an error on null values? Wouldn't it make more sense to search for null values instead?

query

stefanhelzle0001

Brainy

The purpose of this parameter is to support optional filters without the hassle of adding additional code. Your use case does not fit into this assumption, so you might have to change the settings.

abhayd3663

I believe ignoreFiltersWithEmptyValues is very useful if it is used correctly.

nicholasw609

Hi Marco! I've definitely run into this problem before, and as others in this thread have acknowledged, the ignoreFiltersWithEmptyValues parameter in your given logical expression does not work well with your use case. However, with a slight adjustment we can actually get value out of it while still allowing for your null filter!

Basically, you still keep your original query on your given search input, but you add another filter that only applies when your input is null. This covers your null case and your non-null case.

It would look something like this:

a!localVariables(
  local!mySearchString: ri!input_string,
  a!queryEntity(
    entity: cons!My_Data_Entity,
    query: a!query(
      logicalExpression: a!queryLogicalExpression(
        filters: {
          a!queryFilter(
            field: "myField",
            operator: "is null",
            value: "",
            applyWhen: a!isNullOrEmpty(value: local!mySearchString)
          ),
          a!queryFilter(
            field: "myField",
            operator: "=",
            value: local!mySearchString
          )
        },
        ignoreFiltersWithEmptyValues: true
      )
    )
  )
)

To your original point, the only reason this solution works is because of the ignoreFilters value, not despite it. The purpose of that condition is to avoid making us have to wrap each of our filters in an if statement (or something equivalent), which actually makes it quite useful!

If you have any questions about this please let me know, I'm more than happy to provide more explanation [emoticon:c4563cd7d5574777a71c318021cbbcc8]

marcot0005Author

I see, thanks for your response. I do have a question though. Why is this more beneficial than wrapping the two filters in an if statement? Seems about the same functionally as far as I can tell, and it isn't particularly shorter/easier to write out. So what makes this the better option between the two?

nicholasw609

So I'm assuming you mean something like this:

a!localVariables(
  local!mySearchString: ri!input_string,
  a!queryEntity(
    entity: cons!My_Data_Entity,
    query: a!query(
      logicalExpression: a!queryLogicalExpression(
        filters: {
          if(
            a!isNullOrEmpty(value: local!mySearchString),
            a!queryFilter(
              field: "myField",
              operator: "is null",
              value: ""
            ),
            a!queryFilter(
              field: "myField",
              operator: "=",
              value: local!mySearchString
            )
          )
        },
        ignoreFiltersWithEmptyValues: false
      )
    )
  )
)

Personally, I'd argue it looks cleaner to just use the applyWhen/ignoreFiltersWhenEmpty arguments than it is adding an if statement to the query, but functionally they should be equivalent. You could probably get away with doing either, honestly.

It might be weird if you have additional filters on top of this, because you'd have a list of filters as the value for the filters argument in your logical expression, but then one of the values would actually be your if statement. In that case I think it would definitely be cleaner to do it my way instead of using an if statement. That being said, in the code above (where we only have this filter), I think its fair to say it can go either way.

Sign up

Login with SSO

Login to the community

Login with SSO