I feel your pain(I pretty much did everything you described above when developing the code for query 2 in above example where there is a working LIKE binding(the ? thing)). So, the working example of this is above in query 2. Sometime, it’s good to restart anew. My suggestion is to revert to original CUSTOM:diabetes and go from there. Then make my mods above, which should then work for any title with a diabetes in it. (Also, note that by changing it to diabetes alone will then put you to query 3 in the above code (query 1 and query 3 are basically the same in order to support CUSTOM:<title> or just <title> entries.
I figured out a very straightforward way to do the wildcard search for query #1 - the narrative diagnosis like diabetes in rules will pick up anything with diabetes anywhere in the name (even, for example “family history of diabetes”).
Here it is, Brady:
Change the line with `title` in it to this:
"AND locate(?,`title`) " .
The locate function returns zero if the second argument is not a substring and the numerical position (a positive number) if it is. Any positive number satisfies the logical test. Voila.
I removed more than 200 unnecessary records from the rules_filter table. With the fix to the clinical_rules.php file to pick up diagnoses more efficiently, only top level ICD9 codes and/or single word narrative descriptions are necessary. Turns out the CUSTOM:: data has the CUSTOM label in caps for the remaining data. Some of the deleted ones used lower case and may not have been handled properly. Could it be the rules editor that put them in lower case?
Also, I put a rule in for the diagnosis “hypertension” instead of “HTN.”
Once we start using a lot of rules, it will be important to avoid bloat.
Always good to see new ways of doing things, but doesn’t the LIKE above work? The problem with the locate() is that it won’t be compatible with other database such as postgresql. If the locate() were the only way to do this, then would be ok. But since LIKE can also do this, might as well keep it compatible just in case we decide to try to support other database software in the future.
Note that this change will effect all lists titles (medications, allergies, surgeries, medical_problems, etc.), so perhaps it may make sense to control this with global(s). Some may want strict checks on certain categories and non-strict checks on other categories. For example, some may not want something like ‘borderline diabetes’ to get swept into the ‘diabetes’ rules (especially if a clinic wants a separate ruleset for the borderline diabetics). Will be good to hear thoughts of users on this issue.
Check out the default rules_filter entries in the database.sql file. This is the place to optimize things; for example, I don’t really know why I added all those ICD9:250xx redundant entries since the ICD9:250 is there… Should put some thought into the others to ensure we don’t make things not stringent enough (for example are all 648’s diabetes); but would be nice, if possible to reduce these all to just the code without the decimal, which would also greatly improve the performance of the CDR engine. Also adding more titles (such as hypertension) makes sense. Please feel free to modify this toward what you think would be best. Then when have it ready, can also figure out a way to fix these in the patch/upgrade mechanism.
Note they are all CUSTOM by default in caps. If the editor is not using caps, then this would be a bug.
Another option for the %diabetes% issue that would require no code mods in the engine would be to simply store it in the term in the database, so CUSTOM::%diabetes% instead of CUSTOM:diabetes should work. This could be then set in the CDR editor as an option when adding or modifying elements. Then get very granular control the option.
For some unknown reason, even with all your suggestions, I could not get the LIKE command to work for queries 1 and 3 though it does seem to work for query 2. I got one error after another. When I didn’t get errors, it didn’t work either.
the few remaining filters include CUSTOM with upper case only. Nothing now has Custom with some lower case values. I’m not sure where that came from.
All I had to do was to check further down in the Query 2 code and see that I was reading your hint wrong and was using commas alone instead of commas and periods near the “%” entries. I’m now confident that your correction will work. I don’t have time to check it at the moment.