<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:DengXian;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@DengXian";
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:"Segoe UI Symbol";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>I think that there could be merit in having the separate table, although that would require changes to a number of different patron touch points. I don’t know how it would work with the “Import patrons” or “Batch patron modification” or “Batch patron deletion and anonymization” tools (among others). We’d also need to modify patron entry, modification, and self-registration.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I was thinking about your example, and I wasn’t sure how DBIC works with a 1 to Many relationship, so I came up with this example after consulting the wiki:<o:p></o:p></p><p class=MsoNormal>curl -u koha:koha --request GET 'http://127.0.0.1:8081/api/v1/patrons/51' --header "x-koha-embed: extended_attributes" <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I gave the patron multiple extended attributes and it did a fantastic job of including the extended attributes as objects in a list in the JSON output. Awesome! I think that could work really well with phones. (Having flags for “primary” and “sms” could be good in that table.)<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>My only hesitation would be with the time/energy that it would take to refactor Koha completely to use a separate phones table on my own :/. <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If it were a team effort, I’d be willing to help create and execute a plan though. <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>It could also become a blueprint for how to denormalize other data in the borrowers table (like email and address). <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>David Cook<o:p></o:p></p><p class=MsoNormal>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal>Prosentient Systems<o:p></o:p></p><p class=MsoNormal>Suite 7.03<o:p></o:p></p><p class=MsoNormal>6a Glen St<o:p></o:p></p><p class=MsoNormal>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal>Australia<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US>From:</span></b><span lang=EN-US> Tomas Cohen Arazi <tomascohen@theke.io> <br><b>Sent:</b> Thursday, 15 September 2022 9:59 PM<br><b>To:</b> dcook@prosentient.com.au<br><b>Cc:</b> koha-devel <koha-devel@lists.koha-community.org>; Kyle Hall <kyle@bywatersolutions.com>; Martin Renvoize <martin.renvoize@ptfs-europe.com><br><b>Subject:</b> Re: Handling normalized phone number data<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>I insist you should think longer term, and add a separate table. That way you will end up with a simpler (and more performant) query as well:<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>GET /patrons<o:p></o:p></p></div><div><p class=MsoNormal>x-koha-embed: phones<o:p></o:p></p></div><div><p class=MsoNormal>x-koha-query: {"phones.normalized":{"-like":"123%"}}<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>El jue, 15 sept 2022 a las 1:33, <<a href="mailto:dcook@prosentient.com.au">dcook@prosentient.com.au</a>> escribió:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>At the moment, the only solution I can think of is to rewrite the DBIC query after it hits the REST API. I’ve figured out 3 ways to do it:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Current:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>{"me.phone"=>{"like"=>"%020404123456%"}}<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Option 1:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>{"me.phone"=>{"in"=> \["SELECT phone FROM borrowers WHERE regexp_replace(phone,?,?) LIKE ?","[^0-9]","",$query->{'like'}]}<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Option 2:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>\["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"]<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Option 3:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Delete {"me.phone"=>{"like"=>"%020404123456%"}}<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Inject the following into the WHERE via the $attributes: \["regexp_replace(me.phone,?,?) LIKE ?","[^0-9]","","%12345678901%"]<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On a database of over 100,000 patrons, Option 1 takes 3 seconds to return while Option 2 takes .2 seconds to return. Option 3 is a bit harder to test but should be the .2 seconds too.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The problem with Option 2 is that it nukes the original hashref, so if that hashref contains other keys for an OR query, they’d be lost. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I was leaning towards Option 1 until I figured out Option 3.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Forutnately, DataTables sends a particular HTTP header, so I’m able to do the re-writing just for DataTables requests to the API.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’ll be posting the patch to <a href="https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817" target="_blank">https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817</a> shortly.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Even if the community doesn’t go this way, I might use it locally anyway going forward. I’ve started rolling out a similar method for older Kohas because of the number of libraries flagging phone number searching as a problem. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>One way or another it’s a problem that needs to be solved…<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>David Cook<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Prosentient Systems<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Suite 7.03<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6a Glen St<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Australia<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Online: 02 8005 0595<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span lang=EN-US>From:</span></b><span lang=EN-US> <a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a> <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>> <br><b>Sent:</b> Thursday, 15 September 2022 1:23 PM<br><b>To:</b> 'Tomas Cohen Arazi' <<a href="mailto:tomascohen@theke.io" target="_blank">tomascohen@theke.io</a>><br><b>Cc:</b> 'koha-devel' <<a href="mailto:koha-devel@lists.koha-community.org" target="_blank">koha-devel@lists.koha-community.org</a>>; 'Kyle Hall' <<a href="mailto:kyle@bywatersolutions.com" target="_blank">kyle@bywatersolutions.com</a>>; 'Martin Renvoize' <<a href="mailto:martin.renvoize@ptfs-europe.com" target="_blank">martin.renvoize@ptfs-europe.com</a>><br><b>Subject:</b> RE: Handling normalized phone number data</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So you’d add it to the client side, eh…<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I looked through “koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc” and “koha-tmpl/intranet-tmpl/prog/js/datatables.js”, and found the DataTables integration with the REST API to be a bit… obscure. But now I see what you mean about adding an embed option via the kohaTable constructor. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Although like Kyle was saying. Adding a phone number table wouldn’t solve this problem (unless we had a normalized phone number column).<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>That said, we wouldn’t need to normalize the phone number data, if we could use use SQL functions on the left hand side of a comparison: <a href="https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison" target="_blank">https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>It’s doable from a DBIC level but I don’t see how we can do it with the DataTables + REST API integration since it requires Perl-specific syntax that JSON can’t handle. Handing DBIC-like syntax to the REST API makes this really hard. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The only thing I can think to do is to rewrite the query after it hits the REST API. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’ll be writing about that again shortly…<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>David Cook<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Prosentient Systems<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Suite 7.03<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6a Glen St<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Australia<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span lang=EN-US>From:</span></b><span lang=EN-US> Tomas Cohen Arazi <<a href="mailto:tomascohen@theke.io" target="_blank">tomascohen@theke.io</a>> <br><b>Sent:</b> Thursday, 15 September 2022 11:06 AM<br><b>To:</b> David Cook <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>><br><b>Cc:</b> koha-devel <<a href="mailto:koha-devel@lists.koha-community.org" target="_blank">koha-devel@lists.koha-community.org</a>>; Kyle Hall <<a href="mailto:kyle@bywatersolutions.com" target="_blank">kyle@bywatersolutions.com</a>>; Martin Renvoize <<a href="mailto:martin.renvoize@ptfs-europe.com" target="_blank">martin.renvoize@ptfs-europe.com</a>><br><b>Subject:</b> Re: Handling normalized phone number data</span><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>For the REST API you would just add an embed option. Correct.<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>El mié, 14 sept 2022 21:37, <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>> escribió:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I think at some point both the phone and email details should be moved into their own table(s), and then 1 would get a primary flag. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>With the DataTables and REST API, I suppose we could hard-code a join into Koha/REST/V1/Patrons.pm.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If we had a “phone” table, we could potentially store both free-text (potentially formatted) and normalized versions of the phone number. Then we’d just update the patron search to use the normalized version. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>But all of that sounds like a lot of effort :/<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>David Cook<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Prosentient Systems<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Suite 7.03<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6a Glen St<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Australia<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span lang=EN-US>From:</span></b><span lang=EN-US> Tomas Cohen Arazi <<a href="mailto:tomascohen@theke.io" target="_blank">tomascohen@theke.io</a>> <br><b>Sent:</b> Wednesday, 14 September 2022 11:10 PM<br><b>To:</b> <a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a><br><b>Cc:</b> koha-devel <<a href="mailto:koha-devel@lists.koha-community.org" target="_blank">koha-devel@lists.koha-community.org</a>>; Kyle Hall <<a href="mailto:kyle@bywatersolutions.com" target="_blank">kyle@bywatersolutions.com</a>>; Martin Renvoize <<a href="mailto:martin.renvoize@ptfs-europe.com" target="_blank">martin.renvoize@ptfs-europe.com</a>><br><b>Subject:</b> Re: Handling normalized phone number data</span><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Should we also think of moving phones to their own table?<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>El mié, 14 sept 2022 a las 3:43, <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>> escribió:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi all,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’ve been working lately on <a href="https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817" target="_blank">https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817</a> in an effort to let staff users easily search phone numbers in the Patrons module.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thoughts?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>David Cook<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Prosentient Systems<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Suite 7.03<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6a Glen St<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Australia<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br clear=all><o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>-- <o:p></o:p></p><div><div><div><div><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Tomás Cohen Arazi<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Theke Solutions (<a href="http://theke.io" target="_blank">http://theke.io</a>)<br><span style='font-family:"Segoe UI Symbol",sans-serif'>✆</span> +54 9351 3513384<br><span style='font-size:9.5pt'>GPG: B2F3C15F</span><o:p></o:p></p></div></div></div></div></div></div></div></div></div></div></blockquote></div></div></div></div></blockquote></div><p class=MsoNormal><br clear=all><o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal>-- <o:p></o:p></p><div><div><div><div><div><div><div><div><p class=MsoNormal>Tomás Cohen Arazi<o:p></o:p></p></div><div><p class=MsoNormal>Theke Solutions (<a href="http://theke.io" target="_blank">http://theke.io</a>)<br><span style='font-family:"Segoe UI Symbol",sans-serif'>✆</span> +54 9351 3513384<br><span style='font-size:9.5pt'>GPG: B2F3C15F</span><o:p></o:p></p></div></div></div></div></div></div></div></div></div></body></html>