Re: [Gnumed-bugs] Trouble to search last name with apostrophe (single quote)

[Karsten Hilbert]

On Thu, Dec 01, 2011 at 01:41:03AM +0000, Jim Busser wrote:

> I have a patient whose name is like
> 
>       O'Kelly, Michael

One *can* search for "Kelly" (omitting the O') but I admit
one must know that (well, the tooltips says so: "fragments
of first- and/or lastname", but ...).

As for why it doesn't find O'Kelly in the first place -
let's see:

2011-12-01 11:24:29  DEBUG     gm.person 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/business/gmPersonSearch.py::_generate_queries_de()
 #362): [O'Kelly]: not a search term with a "suggestive" structure
2011-12-01 11:24:29  DEBUG     gm.person 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/business/gmPersonSearch.py::_normalize_soundalikes()
 #151): [O'Kelly] -> [O("|'|`|-|\s)*Kelly]
2011-12-01 11:24:29  DEBUG     gm.person 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/business/gmPersonSearch.py::_generate_dumb_brute_query()
 #624): _generate_dumb_brute_query("O'Kelly")
2011-12-01 11:24:29  DEBUG     gm.person 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/business/gmPersonSearch.py::get_identities()
 #81): running {'cmd': u"\nselect distinct on (pk_identity) * from 
(\n\tselect\n\t\tvbp.*, 'kompletter Name'::text as 
match_type\n\tfrom\n\t\tdem.v_basic_person vbp,\n\t\tdem.names 
n\n\twhere\n\t\tvbp.pk_identity = n.id_identity\n\t\t and lower(vbp.title || 
vbp.firstnames || vbp.lastnames) ~* lower(%s)\n\torder 
by\n\t\tlastnames,\n\t\tfirstnames,\n\t\tdob\n) as ordered_list", 'args': 
[u"O'Kelly"]}
2011-12-01 11:24:29  INFO      gm.person 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/wxpython/gmPatSearchWidgets.py::_on_enter()
 #837): 0 matching person(s) found

Hm. Let's dissect the query:

select distinct on (pk_identity)
        *
from (
        select
                vbp.*,
                'kompletter Name'::text as match_type
        from
                dem.v_basic_person vbp,
                dem.names n
        where
                vbp.pk_identity = n.id_identity
                        and
                lower(vbp.title || vbp.firstnames || vbp.lastnames) ~* 
lower('O''Kelly')
        order by
                lastnames,
                firstnames,
                dob
) as ordered_list
;

Oha, vbp.title can be NULL (and often will be) thus making
the lower(...) NULL which of course does not match. Query
bug !

This has not been a problem so far since GNUmed has got a
lot of redundancy built into it when searching for people.

The fixed query is this:

select distinct on (pk_identity)
        *
from (
        select
                vbp.*,
                'kompletter Name'::text as match_type
        from
                dem.v_basic_person vbp,
                dem.names n
        where
                vbp.pk_identity = n.id_identity
                        and
                lower(coalesce(vbp.title, '') || vbp.firstnames || 
vbp.lastnames) ~* lower('O''Kelly')
        order by
                lastnames,
                firstnames,
                dob
) as ordered_list
;

Will fix for the next 1.1 release.

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