How can I use the MySQL FIELD() function within a SelectQuery object in Views?

Question

I want to use the MySQL FIELD() function to alter a query so that it orders a particular field on a particular order of keys.

The keys for this 'select' field are: active, under_contract, sold

I want to sort the View so that all of my results are sorted first by node.created DESC (this is simple, of course) and then push all sold nodes to the very bottom of the result.

I tried doing this first in hook_views_post_execute():

function mymodule_views_post_execute(&$view) {
  if ($view->name == 'my_view') {
    usort($view->result, function($a, $b) {
      return ($a->field_field_status[0]['raw']['value'] == 'sold') ? 1 : -1;
    });
  }
}

but quickly discovered that, since my view only shows 25 results per page, this only pushes sold nodes to the bottom of each page.

Next, I tried to use hook_views_query_alter() to add the awesome MySQL FIELD() function that I just discovered – which works to achieve my goal perfectly in MySQL - but can't be added (at least as I'm discovering) to a SelectQuery object, which Views uses to run the query:

function mymodule_views_query_alter(&$view, &$query) {
  if ($view->name == 'my_view') {
    $query->orderby[0] = array(
      'field' => " FIELD(field_data_field_status.field_status_value, 'sold') ",
      'direction' => 'DESC',
  );
  }
}

How can I add a FIELD() orderby function to this query? Is there another method to push a particular filter value to the bottom of the result queue?

Answer 1

I got this working! I couldn't find any way to get the orderBy working in hook_views_query_alter(), so I tried to do this in the raw hook_query_alter(). In my particular situation, I couldn't get any result when I filtered against the table that Views had already joined with (I can't explain why) and so I added my own join. Voila, it works.

function mymodule_query_alter($query) {
  if ($query->hasTag('my_view_id')) {
    // Remove the current sort order. 
    $order =& $query->getOrderBy();
    $order = array();

    // Join with the table that we need, giving it a custom alias.
    $query->join('field_data_field_listing_status', 'custom_status', 'custom_status.entity_id = node.nid');

    // Add our cool FIELD() function to push all 'sold' nodes to the very back of the list.
    $query->orderBy("FIELD(custom_status.field_listing_status_value, 'sold')", 'ASC');

    // Add any other ordering that we need...
    $query->orderBy('node_created', 'DESC');
  }
}

Answer 2

Building on the previous answer (thanks). I found a way to do it within the views API.

"5,2,1,4" is just a random order of ids, so you can replace this by whatever field and keys you wish.

function HOOK_views_post_build ($view) {
  if ($view->name == 'my_view' && $view->current_display == 'my_display') {
      $view->build_info['query']->orderBy("FIELD(id,5,2,1,4)", 'ASC');
    }
  }
}