Custom/code_types.inc

yehster wrote on Wednesday, January 30, 2013:

Brady,
To allow generic filtering for arbitrary things like the “disorder” clause.
What I’ve done is add  more metadata filters to be included as part of the query  generation.
https://github.com/yehster/openemr/blob/code-type-refactor/custom/code_types.inc.php#L373
My approach seems to be similar to how your are appending “$diagnosis_sql_specific”, but I’m storing them in an array for additional flexibility.

The definition of the metadata happens here
https://github.com/yehster/openemr/blob/code-type-refactor/custom/code_types.inc.php#L81
The “filter_clauses” are hard coded for now, but they could also be updated dynamically based on language settings.

It might even make sense to move the metadata definitions (or overrides) into the code_types table itself (later).

However, I’m not completely sure that filtering by finding,disorder,etc. in the description is the best way to go.  I thought that there is a way to determine what kind of a concept a given SNOMED term is using the relationship data.  That mechanism I think would also be language agnostic.

yehster wrote on Wednesday, January 30, 2013:

Brady,
The more I think about this, I think we should just be searching sct_descriptions in the first place.  No need for some potentially expensive additional joins….

yehster wrote on Wednesday, January 30, 2013:

Or maybe the join with sct_descriptions should actually be a “full join” instead of a left join.  Every sct_concept should have one or more sct_descriptions row….

You are right that the translation of “disorder,finding,etc…” is the wrong way to go.

bradymiller wrote on Wednesday, January 30, 2013:

Hi Kevin,

I’ve been also bouncing around the same thoughts on keeping one table, joins and potentially trying out the relationships table. Still undecided (more testing and playing around to go) and It’s why I want to keep this out of your stuff since I’d like to get your stuff in the 4.1.1 patch while the translation/snomed stuff will be destined for the next openemr version release.

-brady
OpenEMR

yehster wrote on Wednesday, January 30, 2013:

Part of my plan is to write some regression tests that can be run from the command line for these search routines. That way whatever implementation we choose we can have a much higher confidence in its correctness.

I would like to try and bring some additional formality to the development process where possible, and given that this is a core, but relatively simple feature it is a good place to start.

Not going to bother with anything like phpunit yet, but it’s something to consider in the future.

I am not in any huge rush to get the fee sheet stuff integrated in to the master branch. In addition to some final tweaks on the automatic issue/problem management, I’m also writing tests with Watir of the new functionality.

If anyone wants to beta test the fee sheet stuff it in the meantime, I can provide an install file with instructions.

bradymiller wrote on Wednesday, January 30, 2013:

hi,

Testing methods would be very nice and would make it much easier for developers to work in the core code. Note there was a developer several years back who started to implement some automated testing within OpenEMR (the Tests directory) and even had some website running them daily. It was very basic, but  If you are interested, here’s the link:
http://www.open-emr.org/wiki/index.php/Automated_Testing

-brady
OpenEMR

bradymiller wrote on Wednesday, January 30, 2013:

Hi Kevin,

Just to clarify, I am planning to have the SNOMED lookups that are focused on separate terms (disorder, findings,procedure etc. or none; currently it now supports a separate disorder and none lookup; external_id 4 and 7) to be separate external_id’s. This allow using and setting the code_types sql table flags to use them in the correct places within OpenEMR.

-brady
OpenEMR

bradymiller wrote on Wednesday, January 30, 2013:

hmm,

It appears though that disorder and finding should be both considered “diagnosis” codes. Will look into.

-brady
OpenEMR

yehster wrote on Wednesday, January 30, 2013:

Also, some concepts have multiple “types”  for example, Organisms are typically both a disorder (type 1) and (type 3)
Try

select * from openemr.sct_descriptions where ConceptID=‘103441001’;

and see what I mean…

bradymiller wrote on Wednesday, January 30, 2013:

Hi Kevin,

I’m just getting type 1 descriptions (these are the preferred descriptions). Type 2 are synonyms and Type 3 are the Fully Unique Descriptions (basically type 1 with the semantic tag at the end).

-brady
OpenEMR

bradymiller wrote on Wednesday, January 30, 2013:

ok,

The snomed stuff is starting to come together a bit more. Here’s a branch with the original stuff and then a new commit there showing the changes along with bringing in a new SNOMED Procedures code set as a demonstration. So, it appears snomed specific query metadata for this will include the ‘semantic tag’ and the DescriptionType setting. The default setting will be disorder for dx and procedure for procedures with DescriptionType to 1. (Note that this is actually a bit physician dependent; for example if I(clinical oncologist) see a patient with leukemia I’d give them a ‘disorder’ diagnosis, but the pathologist in their report may want to use both the ‘morphologic abnormality’ tag and ‘disorder’ tag; thus the metadata will need to support possible mutliple tags along with being able to set the DescriptionCategory to 3 in this case, because if you are showing more than one semantic tag, will need to explicitly show it so the user picks the correct one.

Best central place to play around with it is in the Fee Sheet. Turn them on in lists->code types first. Then can search all three mechanisms in the fee sheet to get an idea of what i mean (note the SNOMED-CT should not be in the fee sheet since it return everythings, so will fix that bug at some point; the point of SNOMED-CT is to assign list items to a SNOMED identifier (see lists->smoking status to see what i mean in the Code column)).

Here’s the branch:
http://github.com/bradymiller/openemr/commits/SNOMED-spanish-support_2

-brady
OpenEMR

bradymiller wrote on Saturday, February 02, 2013:

Hi,

Here’s the finished/tested code to support import and use of Spanish snomed codes (along with a snomed procedure code type). This is currently based on the master branch. In order to avoid major future conflicts in the custom/code_types.inc.php script, I am not committing this to the official codebase until the project in this thread is complete(and committed to the official codebase). At that time, I will then integrate this Spanish snomed code commit into the official codebase:
http://github.com/bradymiller/openemr/commit/b9571523138124eea72da15b5f43128a6a1d2080

-brady
OpenEMR

yehster wrote on Tuesday, February 05, 2013:

https://github.com/yehster/openemr/tree/code-type-refactor

Brady,
I followed you lead on joining sct_descriptions with sct_concepts to choose a particular “DescriptionType.”  What I determined was that doing so is about 3 times slower for description searches than just using sct_concepts alone.

The problem is searching descriptions is always going to result in a table scan, (an index won’t help because we are doing like %<value>% clauses).  So, either we scan 400K rows of sct_concepts, or 1.2 Million rows of sct_descriptions. 

I have metadata which allows for either search method . The comments in the code explain things more.

yehster wrote on Tuesday, February 05, 2013:

Ok, so flipping the join around seems to speed things up by about 10%.  But it’s still much slower than searching only the concepts table by itself.

yehster wrote on Tuesday, February 05, 2013:

https://github.com/yehster/openemr/tree/code-type-refactor-2

Here’s what I decided to do in the interest of performance.  I believe that semantically, it all behaves the way you intended Brady, but I’m “joining” the other direction.

Also, for generic “SNOMED-CT” where there’s no filter needed for “disorder, procedure, organism, etc…” I don’t bother joining with sct_descriptions. It just slows things down because I think we still want to include the “(disorder)” of a “type 3” description instead of a type 1.  It’s easy enough change the meta data to do the join, but I don’t think it’s worth the performance hit.

Included are some test pages to do lots of different searches of multiple types. 

Whatever terms/phrases for languages, “findings”, “organism” etc… we need to accommodate in the future,  it should be very simple to incorporate.

I also included the “sequential search” wrapper you implemented Brady in this commit.

bradymiller wrote on Wednesday, February 06, 2013:

Very cool,

Starting to test this out. Reasons for joining sct_concepts and sct_descriptions in SNOMED are:
1. sct_concepts does not contain translated descriptions. Wwhich is actually pretty awesome, because then the search for semantic tags (ie. findings, procedures) are language agnostic and the query with the english tag will work on any SNOMED language database.
2. sct_descriptions contains the translated descriptions along with a description type. This is awesome because we can control whether we include the semantic tag in the returned description.

Hence, the full clinical term search actually only needs to go through sct_descriptions (the sct_concepts join isn’t needed since there is no semantic tag).

Hope this makes sense. For testing I’ll merge in the Spanish snomed support on github (won’t touch your code_types searching script, just the importing stuff), so you can get a better idea of how the other language snomed stuff works.

-brady
OpenEMR

bradymiller wrote on Wednesday, February 06, 2013:

Hi,

Overall, testing is looking good so far. To help with testing here’s a branch that rebases Kevin’s stuff to most recent master, adds in the ability to import Spanish SNOMED set, add in support for a Procedure snomed codetype and incorporates the sequential searching throughout the codebase:
http://github.com/bradymiller/openemr/commits/yehster-code-type-refactor-2-with-additional-stuff

The snomed bug noted in spanish is:
1. The description search needs to go through the sct_descriptions table (only the semantic tag search should go through the sct_concepts table, which it seems to be doing nicely on testing). For example, if I search for ‘pain’ now, I actually get items with ‘dolor’ in it since the ‘pain’ search is going through sct_concepts, but a search for ‘dolor’ gives nothing. (and again, as discussed above, the snomed search clinical term search (ie. no sematic tags) just needs to go through sct_descriptions without any sct_concepts join).

-brady
OpenEMR

bradymiller wrote on Wednesday, February 06, 2013:

Hi,

The snomed searching definitely seems way more sluggish than the method here:
http://github.com/bradymiller/openemr/commit/b9571523138124eea72da15b5f43128a6a1d2080

It appears that the only difference is that my method in above commit does:
sct_concepts
LEFT OUTER JOIN
sct_descriptions
LEFT_OUTER_JOIN
codes
(Which I think in effect should then only have several hundred thousands rows to then search through in the WHERE clause.)

Your code does:
sct_concepts
LEFT OUTER JOIN
codes
INNER JOIN
sct_descriptions
(This doesn’t seem much different than my method, but there does seem to be a marked worsening of performance)

-brady
OpenEMR

yehster wrote on Wednesday, February 06, 2013:

https://github.com/yehster/openemr/tree/code-types-language
Now that I understand that sct_concept is always in English, it makes more sense why  to scan sct_descriptions instead of sct_concepts. 

Based on your branch, I switched the metadata back to do
FROM sct_descriptions…
INNER JOIN sct_concepts…
WHERE sct_descriptions…

The relationship between sct_descriptions is many-to-one with sct_concepts.  So every ConceptId listed in sct_description is supposed to appear in sct_concepts.  This is why I did an INNER JOIN instead of an OUTER.  OUTER is just “wasted effort.”  Unless the tables got corrupted, there won’t be any “sct_descriptions” that don’t pair up with an “sct_concepts” row.  But if you specify LEFT OUTER in the query, the database has to work harder to make sure that the case.

LEFT OUTER JOIN will only ever be as fast as INNER JOIN, and can sometimes be slower as you see from your tests.

bradymiller wrote on Wednesday, February 06, 2013:

Yep,

Seeing no real difference in the query in my original commit vs your commit:
Current query mechanism
1345 rows in set (0.40 sec)
mysql> EXPLAIN SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-PR’ as code_type_name FROM sct_descriptions LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ‘108’ INNER JOIN sct_concepts ON sct_descriptions.ConceptId=sct_concepts.ConceptId AND FullySpecifiedName like ‘%(procedure)’ WHERE sct_descriptions.Term LIKE ‘%biopsy%’ AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=1 ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId;
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| id | select_type | table            | type   | possible_keys  | key       | key_len | ref                                | rows    | Extra                       |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
|  1 | SIMPLE      | sct_descriptions | ALL    | NULL           | NULL      | NULL    | NULL                               | 1177657 | Using where; Using filesort |
|  1 | SIMPLE      | c                | ref    | code,code_type | code_type | 3       | const                              |       1 |                             |
|  1 | SIMPLE      | sct_concepts     | eq_ref | PRIMARY        | PRIMARY   | 8       | openemr.sct_descriptions.ConceptId |       1 | Using where                 |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
3 rows in set (0.03 sec)

My original query mechanism
1345 rows in set (0.41 sec)
mysql> EXPLAIN SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-PR’ as code_type_name FROM sct_concepts LEFT OUTER JOIN `sct_descriptions` ON sct_concepts.ConceptId=sct_descriptions.ConceptId AND sct_concepts.ConceptStatus=0 AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=1 LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ‘108’ WHERE sct_concepts.FullySpecifiedName like ‘%(procedure)’ AND sct_descriptions.Term LIKE ‘%biopsy%’ ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId;
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
| id | select_type | table            | type   | possible_keys  | key       | key_len | ref                                | rows    | Extra                       |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
|  1 | SIMPLE      | sct_descriptions | ALL    | NULL           | NULL      | NULL    | NULL                               | 1177657 | Using where; Using filesort |
|  1 | SIMPLE      | sct_concepts     | eq_ref | PRIMARY        | PRIMARY   | 8       | openemr.sct_descriptions.ConceptId |       1 | Using where                 |
|  1 | SIMPLE      | c                | ref    | code,code_type | code_type | 3       | const                              |       1 |                             |
±–±--------±-----------±----±----------±------±-----±-----------------------±-----±------------------+
3 rows in set (0.00 sec)

Note I was able to reduce the scanned queries to about 400K in above EXPLAIN output is set an index in sct_descriptions.DescriptionStatus, however this didn’t seem to shorten the query times at all.

Here’s an error I am noting in Administration->Services if do:
1. Set the search to blank
2. Filter for ‘Diagnosis Reporting Only’ and/ord ‘Service Reporting Only’
3. Search SNOMED
(odd that the error only happens if there are no flagged ‘Diagnosis Reporting Only’ and/or ‘Service Reporting Only’ items)

ERROR: query failed: SELECT sct_descriptions.ConceptId as code, sct_descriptions.Term as code_text ,c.id, c.code_type, c.modifier, c.units, c.fee, c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ‘SNOMED-CT’ as code_type_name FROM sct_descriptions LEFT OUTER JOIN `codes` as c ON sct_descriptions.ConceptId = c.code AND c.code_type = ? WHERE () AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=3 AND c.financial_reporting=‘1’ ORDER BY sct_descriptions.ConceptId+0,sct_descriptions.ConceptId

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) AND sct_descriptions.DescriptionStatus=0 AND sct_descriptions.DescriptionType=’ at line 1

-brady
OpenEMR