How to alter a view sort order?

Question

I have used Views to create a view Display a summary by contextual filter.

O/p

View settings like

Needs

There are only two option for sorting "ASC" or "DESC".

But i need to show like

Pass(4)

Fail(2)

Waiting(1)

Tried for

function hook_views_query_alter(){

}

But did not get how achieve this.

VIEW QUERY

SELECT field_data_field_bid_test.field_bid_test_value AS field_data_field_bid_test_field_bid_test_value, COUNT(crm_core_contact.contact_id) AS num_records
FROM 
{crm_core_contact} crm_core_contact
INNER JOIN {users} users_crm_core_contact ON crm_core_contact.uid = users_crm_core_contact.uid
LEFT JOIN {field_data_field_bid_test} field_data_field_bid_test ON crm_core_contact.contact_id = field_data_field_bid_test.entity_id AND (field_data_field_bid_test.entity_type = 'crm_core_contact' AND field_data_field_bid_test.deleted = '0')
GROUP BY field_data_field_bid_test_field_bid_test_value
ORDER BY field_data_field_bid_test_field_bid_test_value ASC

Answer 1

Yes finally i am able to solve this one.

Using

function utility_views_query_alter(&$view, &$query) {

  if ($view->name == 'Viewname') {

    //dpm($view->query->orderby[0]);
 $view->query->orderby[0]['field'] = "CASE  field_data_field_bid_test_field_bid_test_value WHEN 'Pass' THEN 1 WHEN 'Fail' THEN 2 WHEN 'Waiting' THEN 3 ELSE 4 END";
  }
}

Answer 2

Another approach you may want to consider, is to slightly adapt the labels to be sorted like so:

• 2-Fail.
• 1-Pass.
• 3-Waiting.
• 4-Other.

The sorted version of this list would look like so:

• 1-Pass.
• 2-Fail.
• 3-Waiting.
• 4-Other.

It's what I often do for any software where I somehow want to overrule the whatever sorting (eg to ensure my most often used folder names are always at the top) ... And it works in pretty much every OS I ever used (including mainframes ...). Also for any release of Drupal ...