Experience Builder Query Widget SQL Expression Builder AND with 'contains' not working

Question

Hi everyone,

Has anyone had this problem?

I have 2 clauses in my SQL Expression Builder in the Query Widget. The user can pick multiple counties and then they can pick multiple Eras. I am using "is any of" for the county list and "contains" for the Era list. The AND is highlighted so I understand this to mean I should get this SQL Query, when using the example of the County as Milwaukee and the Era as 19th Century or 20th Century:

County = 'Milwaukee' And (Era LIKE '%19th Century%' Or Era LIKE '%20th Century%')

Is that right so far? I'll admit this SQL Expression Builder has been very confusing.

So when I run this in ArcGIS Pro, I get 60 records. And when I run it in Experience Builder I get 331. In Experience Builder it is giving me all Counties, not just Milwaukee County. I can't replicate this in ArcGIS Pro - i.e. I can't find a SQL query that gives me 331 results.

Can anyone explain this? What is the SQL query that Experience Builder is running?

Thank you,

A

**Edit - uh oh, and if I change my SQL Expression Builder to start with Era and have County second:

Then I get 438 results in ExpB using the same data - Era as 19th Century or 20th Century and county as Milwaukee.

VenkataKondepati · Accepted Answer

I think the issue is operator grouping/precedence.

The query you want is:
County = 'Milwaukee'
AND (
Era LIKE '%19th Century%'
OR Era LIKE '%20th Century%'
)

But the results sound like Experience Builder may be effectively running something closer to:

County = 'Milwaukee'
AND Era LIKE '%19th Century%'
OR Era LIKE '%20th Century%'

Since SQL evaluates AND before OR, that would return:

(County = 'Milwaukee' AND Era LIKE '%19th Century%')
OR Era LIKE '%20th Century%'

That would explain why you’re getting records from all counties.

I would try putting the Era conditions into their own clause set grouped with OR, then combine that whole Era clause set with the County clause using AND.

ryanuthoff · Accepted Answer

I was curious about this so I tried to replicate your scenario in ExB. I was able to replicate it, and I can provide some insight on the query that ExB is actually using.

First of all, you can validate this yourself by opening your ExB app and opening the query widget. Then, open up your browser's dev options to view the internet traffic. From there, you will be able to see the requests that ExB is sending to the server, which includes the query from the query widget. You might have to click a few of the requests to find the one you're looking for, but you will be able to see the query that ExB is sending.

In your case, it appears that ExB is sending a query you're not expecting. This is the query: (County IN ('Lincoln')) AND (Era LIKE '%19th Century%') OR (Era LIKE '%20th Century%')

Notice that if you select multiple eras, it is treating them separately (putting them in separate parenthesis) vs. the query that you are expecting it to be.

So that is the root of the problem and why you're not getting the expected results. I don't know if this is by design or not. Honestly, I would have expected the same behavior you are expecting if I were to configure the SQL Expression Builder the same way you did. In my mind, if a person selects multiple eras, it should put that in one big parenthesis block instead of a parenthesis for each era.

Edit: It looks like someone beat me to the solution lol. And bonus points for them for actually knowing why the problem is happening lol.