Re: [Gnumed-devel] jl-gui et alii - v_emr_tree

[Jerzy Luszawski]

On 19-08-2013 12:20 Karsten Hilbert <address@hidden> wrote:

> I've got a few questions on this view:
> 
> CREATE OR REPLACE VIEW clin.v_emr_tree AS
> (
>       SELECT DISTINCT
>               enc.pk_patient,
>               epi.pk_health_issue AS pk_issue,
>               epi.health_issue AS issue_description,
>               epi.pk_episode,
>               epi.description AS episode_description,
>               enc.pk_encounter,
>               enc.assessment_of_encounter AS encounter_description,
>               enc.started AS encounter_started,
>               enc.l10n_type AS encounter_l10n_type
>       FROM
>               clin.v_pat_encounters enc
>                       JOIN clin.clin_narrative cn ON enc.pk_encounter = 
> cn.fk_encounter
>                               JOIN clin.v_pat_episodes epi ON cn.fk_episode = 
> epi.pk_episode
>       ORDER BY
>               enc.pk_patient,
>               epi.pk_health_issue,
>               epi.health_issue,
>               epi.pk_episode,
>               epi.description,
>               enc.pk_encounter,
>               enc.assessment_of_encounter,
>               enc.started,
>               enc.l10n_type
> ) UNION
>       SELECT
>               v_pat_episodes.pk_patient,
>               v_pat_episodes.pk_health_issue AS pk_issue,
>               v_pat_episodes.health_issue AS issue_description,
>               v_pat_episodes.pk_episode,
>               v_pat_episodes.description AS episode_description,
>               NULL::unknown AS pk_encounter,
>               NULL::unknown AS encounter_description,
>               NULL::unknown AS encounter_started,
>               NULL::unknown AS encounter_l10n_type
>       FROM clin.v_pat_episodes
> ORDER BY 1, 2, 4, 6
> ;
> 
> 
> 1) Do you intend to apply the "ORDER BY 1,2,4,6" to the
>    entire view or to the second UNION sub query ? (if
>    it is the second sub-query the 6 seems unhelpful
>    as it is always NULL)
It refers to whole UNION, by specification of PostgreSQL, AFAIR. That's
why it requires column numbers instead of names. It seems that the
first ORDER BY is superfluous, though. 

> 
> 2) Do you see problems with rewriting the view to
>    directly look at the base tables rather than
>    views thereof ? (that way this view will be more
>    robust when other views are modified)
AFAIK Postgres does good job by optimizing the views by itself.
Moreover it would probably require more nested SELECTS, exactly as the
base views do. I see no benefits (except getting independence from
other views, but in exchange for readability).

> 
> 3) Might the NULL::unknown casts be typed appropriately ?
err... Looks like I have them corrected in my DB but not corrected the
script. 
----
NULL::integer AS pk_encounter, 
NULL::text AS encounter_description,
NULL::timestamp with time zone AS encounter_started, 
NULL::text AS encounter_l10n_type 
----
I will push the commit with correction soon.

> 
> 4) What is the reason for choosing assessment to be the
>    description of the encounter ?  (this will severely limit
>    the generic applicability of this view)
Isn't assessment_of_encounter just this - short description?
At least I use it this way. And I need it to be directly visible and
short, I mean without any additional dates, statistics or whatever
makes more elaborated "description". 

> 5) I think I don't fully understand which expected duplicity
>    you are protecting against with the sub-query global DISTINCT
>    on the first UNION sub-query ?
It is not obvious, I know :) I need *only encounters with at least one
narrative*, so I join with clin.clin_narrative, although I do not use
any of its fields. There can be many narratives... The alternative is
to explicitly look for existing narratives by SELECT in the WHERE
clause.

The general purpose of this wiew is to create pure hierarchical tree of
health_issue->episode->encounter. Additional with no multiple links
between encounter and episodes.
It resolves the problem with encounters not linked directly to episodes.
Quoting our earlier discussion:
----
> And encounter is not linked directly to episode, only
> through narratives.  

Sure, that's quite required. Encounters are multi-problem in
most if not all cases (although many specialities choose to
ignore all but one problem most of the time).
----
I deal with one problem at a time and need the frontend (at least my
plugins) to support this workflow. But I understand that others'
workflow can be different from my narrow specialisation.

-- 
Regards,
Jerzy Luszawski