2

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?

  • Thats not how you add an orderby to a SelectQuery built with db_select(). you use the orderBy() function I believe. See api.drupal.org/api/drupal/includes%21database%21database.inc/… Install the Devel module and dpm($query); in your hook_views_query_alter(), which should be MYMODULE_views_query_alter() .... – tenken Dec 7 '12 at 16:26
  • $query->orderby consists of arrays of [field],[direction]. See: api.drupal.org/api/drupal/includes%21database%21select.inc/…. When Views formalizes the $query array it runs the ->orderBy() method. Since $query in hook_views_query_alter() is simply an array and not yet an instantiation of a SelectQuery object, throwing ->orderBy() at it throws a fatal error, Call to undefined method. – Charlie Schliesser Dec 7 '12 at 16:48
  • For further explanation, see api.drupal.org/api/views/views.api.php/function/…. At this stage, we don't treat this as a true SelectQuery object but an array that describes the query and contains all of the arguments that these methods need (such as orderBy($field, $direction)). – Charlie Schliesser Dec 7 '12 at 16:50
3

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');
  }
}
0

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');
    }
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.