Document query with COLLATION

George Tselios asked on March 5, 2020 10:33

Dear Sirs,

In our current project (Kentico 12SP MVC). we have a list of article pages that are child nodes of a parent container page. In the MVC application, when a user reaches the container page, we need to display an alphabetical filter for the article pages. The filter is a list of all the letters that the title of the articles starts with, for example A B D F G X etc. The list should contain the initial letters of the existing article page titles only and not all the alphabet letters. Once the user clicks on a letter, then a list of the articles whose title starts with the selected letter should be displayed.

We have two questions:

Is there any type of query that given the (parent) container page NodeID may return a list of all the initial letters of the children article pages title? Meaning a type of query that may apply an SQL LEFT or SUBSTRING function and return a list of strings rather than a list of TreeNodes? Of course, if such a query does not exist, we may alternatively, use a DocumentQuery to load all children article nodes and then process each title in order to construct the alphabetical list.
In order to get the list of the article pages given an initial letter, we need to use the StartsWith predicate on the DocumentQuery. The problem we are facing is that the title of the articles is in Greek language, so the same letter may or may not have an accent, but in the alphabetical filter we do not want to use all variations of the same letter regarding the accent. Currently, the StartsWith returns only exact matches because the SQL Server is configured with Accent Sensitivity. Is there a way to introduce the COLLATION parameter in the StartsWith predicate in order to get the correct results? In general, is there a query that may work or even surpass the Accent Sensitivity of the SQL Server when dealing with string fields?

Thanks in advance,

George

Recent Answers

Dmitry Bastron answered on March 5, 2020 12:48

Hi George,

These are interesting questions. Here are my thoughts:

I tried a couple of times to use extra SQL parameters but with no luck. You are right, you can used DocumentQuery to load all child nodes and process titles in code. Make sure you use .Columns(..) method in this case to limit the load to title field only and you can use .Distinct() as well.
It's not possible to pass collation into the API query as per my knowledge. And I think you have two options here:
- Write stored procedure with your custom logic and call SP from the code
- Add multiple StartsWith combined with Or logic: .StartsWith("Title", normalLetter).Or().StartsWith("Title", accentLetter). I believe it should be quite easy to generate accent letter from normal letter.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on March 5, 2020 12:59 (last edited on March 5, 2020 13:14)

Should be something like this:

        var allchirldrenArticles = DocumentHelper.GetDocuments("articleDocumenTtype")
         .WhereEquals("NodeParentID", CurrentDocument.NodeID)
          //.Path("pathToYourNode", PathTypeEnum.Section) - could use section instead of where above if you have more than 1 level
          .Columns("column1Name, DocumentCulture, DocumentName")
         .OrderBy("DocumentName")
         .Culture("en-US")
         .Published()
         .CombineWithDefaultCulture()
         .ToList()

Cache the list and use linq for your text manipulations (i.e. first letter, accents etc). Let say to get first letter should be : allchirldrenArticles .Select(x => x.DocumentName[0])

0 votesVote for this answer Mark as a Correct answer