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

[AndreaB_]

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 solution]

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.

[AndreaB_]

1000% correct, thank you!!! I could not figure this out. 

Yes, if I put (County = 'Milwaukee' AND Era LIKE '%19th Century%') OR Era LIKE '%20th Century%' into ArcGIS Pro I get 331 results. so that is what ExpB is doing. 

Thank you for how to group the clause sets also. This will work. I actually have it slightly more complicated in production because I have another field. But in case this helps anyone here is my final SQL Expression Builder screenshot:

This is so hard to figure out. Esri, can you make this easier, please? Documentation, better UI, let us type in the SQL, something? ugh.

[RyanUthoff · Accepted solution]

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.

[AndreaB_]

Points for you too because I didn't know I could "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." Thank you! 😊

*oh and also, thank you for saying you would have expected the same behavior if you were to configure it the same way. I thought this was a bug!! apparently it's expected - but not by humans. Lol.