Query logic

Question

Hey all, 
 Please provide suggestions for below scenario 
 
 1st row is having a columv value 'GE | SP |US' 
 2nd row is having column value 'USA | CA' 
 
 I need to query the row which is having US or SP in it . I tried the approach of looping(US,SP) the query filter using include parameter. But for US, we are getting both the rows. 
 
 Can you please suggest an approach to query the expected result which is 1st row ? 
 
 Thanks in advance!

Mike Schmitt · Accepted Answer

This is a good example of why, whenever anyone is forced to store an array of data in a text column, i always recommend they at least store it as JSON data. In that manner there are enough delimiters to allow searching on exact substrings (since you'd be able to search on i.e. the exact string "US" including the quotes, eliminating the accidental inclusion of errant matches like "USA" in this case). It also takes a lot of the "guesswork" out of parsing the stored contents back into an array (you don't have to worry about writing your own parser and handling edge cases, since JSON takes care of most of that for you). 
 I guess my question for your case is, do you have alternative options availalbe like writing a View to pre-parse the values a little bit (i think you could probably split multiple values across the [" | "] string, then handle them better, and query from that), or perhaps create a new DB column that could store the same data but parsed into a more readable format? (Or even a link table where you add an independent entry for each item in the original set?) Because without a more deluxe solution like that, I'm not sure you'll be able to solve all the corner cases you are likely to run into with your current set-up.

Yogi Patel · Answer

As suggested by Stefan Helzle and Mike Schmitt , you should change the database design if possible. If that option is not available, you can try the following query to get the desired result: 
 a!queryEntity(
 entity: cons!YD_DT_DEMO,
 query: a!query(
 logicalExpression: a!queryLogicalExpression(
 operator: "OR",
 filters: {
 a!queryFilter(
 field: "name",
 operator: "in",
 value: { "|US", "|US|", "US|" }
 ),
 a!queryFilter(
 field: "name",
 operator: "in",
 value: { "|SP", "|SP|", "SP|" }
 )
 }
 ),
 pagingInfo: a!pagingInfo(startIndex: 1, batchSize: - 1)
 ),
 fetchTotalCount: false
) 
 Note: You can write code to generate the value of in parameters { "|US", "|US|", "US|" }.

Query logic

Top Replies