Patron Search by date of birth

Bug #1655158 reported by Blake GH on 2017-01-09
This bug affects 6 people
Affects Status Importance Assigned to Milestone

Bug Description

We have more than one library that would like to input a date of birth when searching for patrons. This helps filter the results when searching for common names. It also helps staff detect a less-than-honest patron.
IRC Discussion:

Blake GH (bmagic) on 2017-01-09
description: updated
Terran McCanna (tmccanna) wrote :

Yes, our libraries have wanted this for a very long time as well.

Changed in evergreen:
status: New → Confirmed
importance: Undecided → Wishlist
tags: added: staffclient webstaffclient
Blake GH (bmagic) on 2017-01-12
description: updated
Galen Charlton (gmc) wrote :

Equinox has been commissioned to produce detailed specs for this proposed feature, using this statement of requirements as a model:

[1] Add a field to the Patron Search interface allowing staff to search by Patron Date of Birth.

Like other date entry areas in the Web Staff Client, both typed entry and calendar widget entry will be supported.

[2] Searching will be available via full birth date.

Use case:
Search by patron full date of birth to verify identity at registration or checkout, in order to:

* Distinguish among patrons with the same name
* Determine if a patron was registered under a previous name
* Verify identity of a patron lacking outside identification

We'll post them here once available.

Blake GH (bmagic) wrote :

It's definitely easier to require a "whole" date but do we want to break it down into month/day/year? Allowing staff to search by year or month or day independently? I can see this being handy and perhaps easier to use?

Blake GH (bmagic) wrote :

Here is the work I have:;a=shortlog;h=refs/heads/user/blake/LP1655158_patron_search_by_date_of_birth

Also, I thought it was worth showing a photo of the interface

tags: added: pullrequest
Changed in evergreen:
assignee: nobody → Terran McCanna (tmccanna)
Terran McCanna (tmccanna) wrote :

Hi Blake - I really like having the date search fields broken up so that we can limit by say, year, rather than having to limit by a specific date, and my tests worked well.

The only issue I can see is that some users will type in "07" instead of just "7" for a month or day, which gives zero results. Would you consider stripping off any leading zeros to accommodate the people who type it in that way?

Changed in evergreen:
assignee: Terran McCanna (tmccanna) → nobody
Blake GH (bmagic) wrote :


Ah yes, good call. My approach for that wouldn't be to strip of preceding 0's. I think I would like the database to include preceding 0's when parsing the date for comparison. This would make it work when someone includes the 0, or doesn't. Because the query looks to see if the day/month/year has any part of the search number. There is a drawback or maybe it could be considered a feature. To make it more clear:

Search for "9" in the year
and you will get results for anyone who has the number "9" in their birth year:
will match

Same for month and day. Search for "7" for the day and you will get:
07 and 17 and 27

I personally don't think this is a problem. In fact, I thought of this during development and decided that it was better than strict interpretation of the users input. If they only enter a single digit into any of the boxes (garbage in, garbage out) - if they want better results, then they can enter a two digit month and day and a four digit year.

I like it when I can type less and basically get what I want. For example, I can just search for the two digit year and still get narrowed results. Combined with a first name or a last name, I think I would get a single hit most of the time!

Patch incoming to enforce the database extraction of the month/day to two digits.

Terran McCanna (tmccanna) wrote :

Sounds good! Typing in something like 197 to narrow down all the 1970s will be handy, too.

Blake GH (bmagic) wrote :


I updated the branch to (hopefully) function as mentioned. The link is the same as above. I would appreciate a second set of eyes.

I ended up having to use a while loop instead of the join map grep. Perl was hating me. The inline if statements were not making the right decisions against $_ .

inside of the greater string concat
 . ( s/year//g ? '' : "RIGHT('0'|| " ) .
was sometimes evaluating "1" when it shouldn't. Perl is suppose to return the number of times it made the replacement. I expect to get "1" when the string appears and "0" when it doesn't which works perfectly in the inline if statement. Except when it doesn't. The while loop is safer.

Terran McCanna (tmccanna) wrote :

Great, we will test!

Changed in evergreen:
assignee: nobody → Terran McCanna (tmccanna)
Terran McCanna (tmccanna) wrote :

I've tested this against our large data set with full dobs and partial dobs and all of my variations have worked well!

I have tested this code and consent to signing off on it with my name, Terran McCanna and my email address, <email address hidden>.

tags: added: signedoff
Changed in evergreen:
assignee: Terran McCanna (tmccanna) → nobody
Changed in evergreen:
milestone: none → 3.0-alpha
Bill Erickson (berick) wrote :

Getting merge conflicts attempting to cherry-pick into a master-based branch. Likely due to the patron search guts being moved into a service. Replacing pullrequest with needsrepatch.

tags: added: needsrepatch
removed: pullrequest
Blake GH (bmagic) wrote :

Yuck - patching coming as soon as I can.

Blake GH (bmagic) wrote :


I have force pushed my branch. Let me know how it shakes out for you.

tags: added: pullrequest
removed: needsrepatch
Bill Erickson (berick) on 2017-08-30
Changed in evergreen:
assignee: nobody → Bill Erickson (berick)
Bill Erickson (berick) wrote :

Code reviewed and tested. Looks good, however I am wary of the wildcard matching as it stands. I expect users will be confused by searching for month=2 and getting results for Feb. and Dec. And I can't imagine a use case where that's terribly helpful.

I propose we make the day and month fields be exact matches, adding preceding zeros where necessary (so '1' will match '01'), and have the year field be left-anchored, to accommodate the 197* query.


Galen Charlton (gmc) wrote :

+1 exact match on day and year and left-anchored years.

Galen Charlton (gmc) wrote :

er, exact match on day and month

Blake GH (bmagic) wrote :

I think the exact match for month and day is solid. I would like to argue for the year though. Consider searching for "76" with the intent 1976. It might be better if we leave it the way it is and allow the year to match any sequence of numerals entered into the input box?

Terran McCanna (tmccanna) wrote :

I'm with Blake. I'd like to be able to enter 76 with the intent of 1976 or 197 with the intent of anything in the 1970s.

Exact match for month and day by way of adding preceding zeros when necessary sounds perfect.

Blake GH (bmagic) wrote :

I went ahead and pushed a change to the branch that will force a two digit month and day. Let me know how that looks.

Jason Stephenson (jstephenson) wrote :

Two year dates get tricky, and since we're talking about dates of birth, the dates should be in the past, but I can see this type of thing being requested for other dates, like expire date, for instance. Then, you can get into some questions that you might not have considered.

For instance, does 76 mean 1976 or 2076? Right now, it may seem obvious, but in a decade or two that decision will be less clear.

How 'bout 20, 30, 40, or 50?

What about 19? Does that mean 19XX or 1919 or 2019? This last is in the future, but close enough that I think the problem becomes apparent.

Also, what about other countries' date conventions? Not everyone enters dates as mm/dd/yy, or even in numerals. Not everyone uses the same calendar. Are those being taken into account?

The decisions made today are going to carry forward for a few years, at least. And I often find that trying to guess what the user wants can lead to more frustration than requiring them to be explicit about what they do want.

Mike Rylander (mrylander) wrote :

I agree with exact match for month and day that Bill brings up. Thanks for making that change.

I don't know what the loop-based implementation looks like, but if it uses regexen to test for specific key string content we should only need a match expression rather than substitution, no? Or, just an eq expression for, say, 'dob_year'?

Thanks, Blake!

Kathy Lussier (klussier) wrote :

I would agree with the comments about two digits for a year if we were forcing the user to enter just two digits. However, they have the option to enter either two or four or even three. If the standard in a country is to enter the year as four digits, the search form accommodates that, right? If somebody enters 15 and retrieves patrons with a DOB of 1915 and 2015, they can then further limit their search by using all four digits.

I would be less inclined to support two-digit entry for years if it were a data-entry form.

Blake GH (bmagic) wrote :


To be specific, the join/map/grep expression creates a SQL expression that looks like this:

JOIN (SELECT id as id FROM actor.usr u WHERE RIGHT('0'|| CAST( DATE_PART('day', dob ) AS text ), 2) ~ ? AND RIGHT('0'|| CAST( DATE_PART('month', dob ) AS text ), 2) ~ ?) AS search ON ( =

Here we have postgres doing string matching just like we do for the first_given_name and most of the other fields on the search form.

The javascript is responsible for forcing the day and month to come back to with two digits.

I think I have addressed the concerns brought up by Jason in my comment #6. The concern about different date formats are currently accommodated because each component of the date is separate in the UI. This approach is ultimately better than any other UI that I could dream of. This allows the staff to search by a single component of the birth date without having to chose a specific date in the century using any "date picker" UI. "Keep it simple stupid" came to mind when developing this.

Here is the while loop that could replace the join/map/grep

    # while (($key, $value) = each (%$search)) {
        # if($$search{$key}{group} eq '4') {
            # my $tval = $key;
            # $tval =~ s/dob_//g;
            # my $right = "RIGHT('0'|| ";
            # my $end = ", 2)";
            # $end = $right = '' if lc $tval eq 'year';
            # $dob .= $right."CAST(DATE_PART('$tval', dob) AS text)$end ~ ? AND ";
        # }
    # }
    # # Trim the last " AND "
    # $dob = substr($dob,0,-4);

The while loop makes it more clear, but it's many more lines. It's nice that this logic can be in a single line! I think the code is cleaner "looking" without it. And the join/map/grep conforms to the rest of the code in that block.

Bill Erickson (berick) wrote :

Day and month were my main concern. I don't have a strong opinion on the year handling. Thanks for the new patch, Blake. Re-testing...

Mike Rylander (mrylander) wrote :

Thanks, Blake. I think the loop is an order of magnitude more readable maintainable, and definitely the right choice a the implementation level. We're talking about a loop of, at most, three executions of six lines of code, most of which are setting and concatenating variables. Perl would be doing that anyway internally in the implicit loop generated by them map, so we're not saving anything there, really, and since you're only executing a single regex in the loop version, instead of up to 3 in the nested ternary, I suspect we end up saving cycles.

Blake GH (bmagic) wrote :


Fair enough. Patch committed (sorry Bill).

Bill Erickson (berick) wrote :

I'm going to bounce it back to you for cleanup, Blake. Can you please squash this all down to 1 commit (or 2 if you prefer a separate commit for release notes) and add another comment to the release notes describing the different behavior of day/month vs. year? I started one you can use if you like:

+* Day and month values are exact matches. E.g. month "1" (or "01")
+ matches January, "12" matches December.
+* Year searches are "contains" searches. E.g. year "15" matches 2015,
+ 1915, 1599, etc. For exact matches use the full 4-digit year.

Blake GH (bmagic) wrote :


It is a single commit. Bill, thanks for the notes, I included them and I couldn't think of anything to add. Let me know if I need to change anything.

Bill Erickson (berick) wrote :

Thanks, Blake. Signed, sealed, delivered (to master).

Changed in evergreen:
status: Confirmed → Fix Committed
assignee: Bill Erickson (berick) → nobody
Blake GH (bmagic) wrote :

Thanks everyone, I love the workflow and the community!

Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers