Use Snippet variables as conditions to prefilter Data Blaze data prior to reading

Questions and Help
1

I have a data table with over 10,000 rows (72,000 actually). I can write a TextBlaze snippet to read the table and use a variable as a condition to filter the results. However, this only appears to work for the first 10,000 rows. DataBlaze filters work as expected when applied in the DataBlaze environment. Is there a way to "pre-filter" the DataBlaze table, using a variable set in a snippet, prior to loading the results into the TextBlaze snippet results area? One example would be like a dictionary with 72,000 entries. If I enter "z" as the letter to start the word, I would want to apply that filter in DataBlaze first, then export those results (assuming they are less than 10,000) to TextBlaze. I'm okay if this needs to be a two-step process where I finish the variable string prior to exporting it to DataBlaze to be used as a filter.

2

Welcome to the community @Wes_Valdes!

One way to do this is to use where clause (Data Blaze | BSQL Reference). I've added a small example which uses variable in a snippet and uses it to filter results of dbselect.

In the example, the table contains 2 rows, but dbselect will return only 1.

{count=12}
{dbselect: SELECT Name, Count FROM Table1 WHERE count=@count; space=id; menu=yes}

3

Thanks! I also just realized that this filter function is case specific. "Chronic" leads to a different set of results than "chronic".

4

Follow up to the previous question. Things are working pretty well if I type in the words in exactly the correct order. I fixed the case issue by making everything lower case. I did make a search function with AND that searches for two phrases and that works too.

My next step is to take a string of words, split them into a list, then use that list to create a dbsearch function using each of the individual words to look for a match with a map function. Neat huh? Except that I can't get it to work. Any ideas?

{formtext: name=lotsofwords; cols=75}
{words=split(lotsofwords, " ")}

{dbselect: SELECT Code, Formal, description FROM ICD10 WHERE {=join(map(words, w -> "contains(description, '" & w & "')"), " AND ")} LIMIT 1000; space=2YRnBzdFZbQLXsTOD09MxW; menu=yes; cols=150; trim=left}

5

Hi Wes,

You can use the lower() function to lowercase strings as part of your query, e.g., lower(field) = "chronic" will match when the field is "chronic", "CHRONIC" or "Chronic".

You cannot dynamically generate BSQL clauses like you are doing where you are creating it by concatenating strings for "AND" clauses.

What you can probably use is a regular expression to match multiple words.

For example:

{words=["cat", "dog"]}

Has "dog": {=testregex("There once was a dog", "(" & join(words, "|") & ")" , "i")}

Has "cat": {=testregex("There once was a Cat", "(" & join(words, "|") & ")" , "i")}

Does not have either "dog" or "cat": {=testregex("There once was a parrot", "(" & join(words, "|") & ")" , "i")}

Notes:

  • use the "i" flag for "ignore case"
  • Regex is "(cat|dog)", this means match "cat" or "dog", you can have any number of words separated by "|"
  • You need to ensure your words don't have special regex characters like "|" or "(". If you do, you can escape them with backslashes.
  • You can try out regexes on a site like https://regex101.com/
6

Thanks, and that is pretty cool. I'm not sure how to implement the textregex into the dbselect formula as the phrase to test would be the 70K+ database entries.

My goal is to have the user

  1. enter a phrase into a text field
  2. use the words of that phrase to return results from datablaze with all the rows that contain those words.
  3. For example, entering "Chronic osteomyelitis left foot" would return all the rows out of about 70,000 that contain those words.

This works using two variables which are text fields named search_1 and search_2:

{formtext: name=search_1; cols=75}{endnote: trim=left}
{formtext: name=search_2; cols=75}{endnote: trim=left}
{dbselect: SELECT Code, Formal, description FROM ICD10 WHERE contains(description, @search_1) AND contains(description, @search_2) LIMIT 1000; space=2YRnBzdFZbQLXsTOD09MxW; menu=yes; cols=150; trim=left}

I was just trying to make the process a little cleaner with a map() but I see your point that isn't an option. Maybe I'll try to split the phrase into a list and then assign variables to each word in the list and try the approach above.

7

Here's an example using testregex:

{words=["Chronic", "other", "Another"]}
Matching Description rows (rows that contain at least one of the words in the description):
{dbselect: SELECT Name, Description FROM Table1 WHERE testregex(lower(Description), {="(" & lower(join(words, "|")) & ")"} ); space=id; menu=yes}

Your approach with multiple distinct contains would also work.

(Note Data Blaze doesn't currently support the "i" modifier so we use lower() in this example to do case-insensitive matching.)

8

Ah, thanks again. I'll play with that. I've been avoiding regex so this is a good opportunity to work with it.
This also worked but not as elegant:

Code: {=code}
Description: {=formal}
{note}{formtext: name=search_1; cols=30}
{endnote: trim=right}
{note}{formtext: name=search_2; cols=30}
{endnote: trim=right}
{note}{formtext: name=search_3; cols=30}
{endnote: trim=right}
{note}{formtext: name=search_4; cols=30}
{endnote: trim=right}
{note}{formtext: name=search_5; cols=30}
{endnote: trim=right}
{note}{formtext: name=search_6; cols=30}
{endnote: trim=right}

{dbselect: SELECT Code, Formal, description FROM ICD10 WHERE contains(description, @search_1) AND contains(description, @search_2) AND contains(description, @search_3) AND contains(description, @search_4) AND contains(description, @search_5) AND contains(description, @search_6) LIMIT 1000; space=2YRnBzdFZbQLXsTOD09MxW; menu=yes; cols=150}

9

The regex seems to work with OR logic so returns all rows that contain ANY of the words. Fortunately, this is exactly what I needed in a different project so that is helpful but for this project I was looking to return the rows that contain ALL the words in the list and I'm not sure how or if that is possible with a regex - but I suck at regex so ...

10

Unfortunately, I don't think that can be expressed in a regex.

11

Ah, bummer. Oh well, the clunky way works so I guess we'll stick with that.