Search for Address Hierarchy based on name searches by a “starts with” query. Relevant code here.
We have a situation where all names for a particular section of the hierarchy starts with the same text. The address hierarchy in question is “Ward”, and the values range from "Ward No. ", followed by numbers. eg: Ward No. 1
Would it be ok to make the module search anywhere in the text instead of “at the beginning”?
I would imagine this working best if we change things to search at the beginning of any words in the text, rather than anywhere at all in the text. Would this work for you?
If I remember correctly, I think “start with” is used for performance reasons. We’ve got an index on the “name” column, and I believe that indexes can only be utilized with “starts with” queries.
Looking quickly, I see two places in the DAO where a starts with query is used–in “getAddressHierarchyEntriesByLevelAndLikeName” and “getAddressHierarchyEntriesByLevelAndLikeNameAndParent”.
I’d want someone to do a little testing, but my hunch would be that switching the “ByLevelAndLikeNameAndParent” to an anywhere search would be okay because the search set should be limited to a set of child entries of a single parent. Do you know if this is the relevant methods for your use case?
Thanks @mogoodrich. I tried switching getAddressHierarchyEntriesByLevelAndLikeNameAndParent to use anywhere, it works pretty well for an address hierarchy of ~50k rows. This is the relevant method for our use case, so it should be fine.
In the meanwhile, I noticed some issues with the implementation of AddressHierarchyAjaxController.getPossibleAddressHierarchyEntriesWithParents. Details are updated in ADDR-74 along with a pull request.