How to ensure sort order with delimited list in WHERE IN clause

David Forster asked on March 7, 2019 16:39

We have a stored value on a web part which is a PIPE separated list of NodeGUIDs, which also indicate the custom sort order of the selected items.

How would we use the stored value to retrieve the list of nodes in the correct order when using this stored value in the application i.e. via Pages API

Iterating thru each piped value and retrieving the page seems excessive/inefficient and a "where IN (..., ..., ...)" approach seems more logical - however sort order is not ensured using this option.

How can you ensure the list of pages returned are in the same order as our PIPED list ?

Correct Answer

Peter Mogilnitski answered on March 7, 2019 18:45

you can use simple macro like below to do that:

{%
test = "guid3|giud1|guid2|guid4";
orderby = "";
i = 0;
foreach (val in test.Split("|", true)) {
  orderby += " when '" + val + "' then " + i;
  i += 1;
}
return  " case nodeguid " +  orderby + " end ";
|(identity)GlobalAdministrator%}.i.e replace test with YourFieldName. Is this custom web part or just a repeater? 


I am not quite sure of your set up. Screenshot would help :).
1 votesVote for this answer Unmark Correct answer

Recent Answers

Peter Mogilnitski answered on March 7, 2019 18:30 (last edited on March 7, 2019 18:33)

I dont know your query but you create order by for example: assuming you have (guid1, guid2, guid3, ....)

SELECT * FROM table1
WHERE NodeGUID IN (guid1,guid3,guid3,guid4)
ORDER BY 
     CASE NodeGUID 
     WHEN 'guid1' THEN 1
     WHEN 'guid2' THEN 2
     WHEN 'guid3' THEN 3
     WHEN 'guid4' THEN 4
     END
1 votesVote for this answer Mark as a Correct answer

David Forster answered on March 7, 2019 18:38

Hi Peter,

while that is technically correct, the node guids are changeable and of any length, and re-orderable.

Dynamically building that order by clause would be a nightmare

0 votesVote for this answer Mark as a Correct answer

David Forster answered on March 7, 2019 18:52

interesting.. i'll give that a try

thanks

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.