Incorrect or Unexpected Search Results in Persona Bar User manager

[moorecreative]

Description of bug

There are unexpected search results when using the Persona Bar's Users management section in DNN 9x.

Sebastian @sleupold mentioned in a conversation in 2018 that the Users Search is now back to being database driven and that we could expect the following with it:

user search functionality degraded since 9.1; suggest improvement #2393
...database search (DNN 9.0 and 9.1.x used Lucene search index). You should be able to append "%" to perform a left search - usually this is performed while you type and exact search upon enter.
Search includes fields username, email. firstname, lastname and displayname.

I have experienced a confusing set of results when performing searches in 9.2.2... in one example, the following users had unexpected results

cjones carly jones
bjones brian jones
ajones alex jones

when performing a search for "jones", only bjones would return in the results. ajones and cjones were not in the search results. However, if I performed a search specifically for "ajones" or "cjones" then a record would show in the search results.

Their records were the same in that they each had usernames and displaynames with "jones" in the wording, however the firstnames and lastnames were blank.

I believe that they had the User table's UserName and DisplayName fields filled in, however had the User table's FirstName and LastName fields empty and so the search for "jones" did not find a value in the LastName field.

Even when the UserProfile table's records for that user included FirstName and LastName values, it would see that the search does not take them into account and only searches the Users table values.

Otherwise, within the Users The DisplayName in both examples the first and last names were present with with a space between them. It seems that the search did not evaluate those as individual words, so a "jones" search did not return results for "alex jones", however a "%jones" search did return all three.

Steps to reproduce

List the steps to reproduce the behavior:

1. Create a set of users similar to the ones in the description, ones where there are FirstName and LastName in the Users data table, but not within the UserProfile table.
2. Go to the Users manager in Persona Bar
3. Perform a search on First Name or Last Name elements to see if they work in results as expected

Current result

I believe that, if the user information of FirstName and LastName are not present within the Users table fields then they are not available to the search query. My hypothesis is that the search query does not include the UserProfile table's values for FirstName and LastName for it's search.

Suggestions

I would recommend that we adjust the search index to also include the UserProfile table's FirstName and LastName data records to better improve the search results and that the evaluation of the DisplayName field could be improved to better recognize portions of words within that data.

Affected version

• 9.3.2
• 9.3.1
• 9.2.2
• 9.2.1
• 9.2
• 9.1.1
• 9.1
• 9.0

[sleupold]

When I redid the stored procedure for DNN 9.2.0, I optimized search in the following columns:

• user.Name (login name)
• user.DisplayName
• user.Email
• user.FirstName
• user.LastName

I replaced search within the fields with left search, to support type suggest and search in large tables. My idea was using left search, while typing and exact match, when hitting enter or selecting an item from the suggest list..
We could add search in (selected) profile columns as well, but this had not been a requirement and will affect performance.

[valadas]

I can reproduce this issue, now is this by design for performance using the left search or should this be an 'includes' search instead ? Opinions ? I also found a related issue that I will log separately but the search only shows when some type of users is selected in the dropdown... IE you cannot search for deleted users or superusers or more importantly all users.

[sleupold]

@valadas SQL Server Indexes (not full text indexes) always order values from the left, i.e. any search in the middle of a value requires a full table scan, which is usually significantly slower or large data sets. Besides, 99% of the people would start typing the first letters of a search term, i.e. "Dan" and not "iel", if searching for you. In case, someone needs to search in the middle of a property, he may preceed the seach term with an asterisk, i.e. "*iel".

[sleupold]

PS: we could overcome the indexing limitation by using a full text index, however, this requires full text catalogs to be turned on in SQL server, which might not be supported by all hosting scenarios.

[valadas]

Oh, I did not know that * worked here, good to know, probably just needs to be documented for a part of this issue

[valadas]

I created DNNCommunity/DNNDocs#359 in the docs, if you have any other search info we can add it there

[valadas]

And I submitted a PR in AdminExperience to allow searching for any type of user.

[valadas]

Merged