Views natural sort with ignoring 'a', 'an' and 'the' in starting of title with numbers before alphabets in title

Public

Recently i has the requirement to sort views data with node title in such a way that ignore the 'a' 'an' and 'the' in node title and if node title have number then those node should come before the node title those completely have alphabets. views natural sort does the same https://www.drupal.org/project/views_natural_sort, but it was failing to index the data because i was having lots of nodes around 2.5k nodes. Views natural sort module does it best way by storing the field which you want to use for sorting in separate table. Here i am sharing snippet which may help you. Ofcourse it will slow down the query and not the best way.

</> CopyGet raw version
php
  1. /**
  2.  * Implementation of hook_views_query_alter
  3.  * @param type $view
  4.  * @param type $query
  5.  */
  6. function MYMODULE_views_query_alter(&$view, &$query) {
  7. if('check appropriate condition'){
  8. //remove all the sort order fields.
  9. $query->orderby = array();
  10. // Add sort criteria which will ignore the 'a', 'an' and 'the' show number before the alphabets in titles. Here sort critaria order is important don't change the order.
  11. //use CASE with regular express to check starting of title with 'a', 'an' and 'the' followed by space
  12. $query->orderby['0']['field'] = "
  13. CASE
  14. WHEN field_data_field_display_title_field_display_title_value REGEXP '^(A|An|The)[[:space:]]' = 1 THEN
  15. TRIM(SUBSTR(field_data_field_display_title_field_display_title_value , INSTR(field_data_field_display_title_field_display_title_value ,' ')))
  16. ELSE field_data_field_display_title_field_display_title_value
  17. END";
  18. $query->orderby['0']['direction'] = 'ASC';
  19. // this is trick which add 0 to the field to cast it to number then check the condition
  20. $query->orderby['1']['field'] = 'field_data_field_display_title_field_display_title_value+0<>0';
  21. $query->orderby['1']['direction'] = 'DESC';
  22. $query->orderby['2']['field'] = 'field_data_field_display_title_field_display_title_value';
  23. $query->orderby['2']['direction'] = 'ASC';
  24. }
  25. }