~ 2 min read
Views MySQL OrderBy – Drupal module
If you’ve ever needed to customize a bit the SQLs in Drupal’s Views module (version 2 for the sake of this argument) you know how much of a pain or ugly-ness is involved. Probably, the first step would be to locate a contributed Views module that would provide the functionality required and then it’s ofcourse required to code review it, make sure it’s properly maintained and not another dead project on Drupal. That’s the “easy way out”.
What if you can’t find one? then you start hooking into dark places like hook_views_pre_build(), hook_views_pre_view() and their sisters, god forbid. Well then, I’ve chosen to go the generic path, that one road that’s less travelled by, and worked out one of those Views contributed modules for your future use.
The use case is requiring to sort a result set by a string field’s values that you defined, which is not ascending or descending. For example, if you wanted to sort a CCK select dropdown field which describes an entity (or node) type, given these values: animal, human, alien. If you wanted to sort the result set listing in an order where animal is first, human later and alien last then using the default DESC or ASC sort order doesn’t really help.
MySQL introduces the ORDER BY FIELD(field_name, field_options...) query signature where it’s possible to specify the field name and (all) of it’s options in exact order that you wish to sort by. If you do not specify all of the field options then you’re in for a surprise because MySQL will sort with the fields you didn’t provide first (it’s possible to mitigate that with specifying the DESC/ASC suffix to the FIELD() function)
The module found it’s way to Drupal, named surprisingly: View MySQL Orderby
Views resources:
- Views handler class
- Views add_orderby()
