Re: [Gnumed-devel] Sort drugs by frequency of use

[Karsten Hilbert]

On Fri, Jun 14, 2013 at 05:53:55AM +0000, Vaibhav Banait wrote:

> Thanks for providing with indian monocomponent drug
> database. But new solutions result in new problem. Now I
> have so many drugs in database, that selecting a single drug
> start taking longer as i have to go through many drugs
> before I select on. This is especially true if brand name is
> short two letter word. Is there any way I can decide
> favourite or if the list itself gets sorted according to
> frequency of use? 

Richard will probably laugh his head off because he's been
saying this, like, 20 years ago ?

Now, for starters I've made the substance selection
phrasewheel pay heed to what's been used before. In the
screen shot note how Ibuprofen 400 and 600 are offered first
(because they've been used before - see 400 in Kirk's plan
on the left while 600 is used in another patient) and 200
and 800 are offered second (they haven't been used yet). For
debugging I have added the "in use" / "not used" labels to
the list items. Note also, how each "group" will properly
sort within itself: 4 before 6 and 2 before 8.

In case anyone cares here's the match provider SQL:

                SELECT
                        data,
                        field_label,
                        list_label,
                        rank
                FROM ((
                        -- substance intakes which match are first
                        SELECT
                                pk_substance AS data,
                                (description || ' ' || amount || ' ' || unit) 
AS field_label,
                                (description || ' ' || amount || ' ' || unit || 
' (in use)') AS list_label,
                                1 AS rank
                        FROM (
                                SELECT DISTINCT ON (description, amount, unit)
                                        pk_substance,
                                        substance AS description,
                                        amount,
                                        unit
                                FROM clin.v_pat_substance_intake
                                WHERE pk_brand IS NULL
                        ) AS normalized_intakes
                        WHERE description %(fragment_condition)s
                ) UNION ALL (
                        -- consumable substances which match - but are not 
intakes - are second
                        SELECT
                                pk AS data,
                                (description || ' ' || amount || ' ' || unit) 
AS field_label,
                                (description || ' ' || amount || ' ' || unit || 
' (not in use)') AS list_label,
                                2 AS rank
                        FROM ref.consumable_substance
                        WHERE
                                description %(fragment_condition)s
                                        AND
                                pk NOT IN (
                                        SELECT fk_substance
                                        FROM clin.substance_intake
                                        WHERE fk_substance IS NOT NULL
                                )
                )) AS candidates
                ORDER BY rank, list_label
                LIMIT 50

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

screenshot_001.png
Description: PNG image