How to replace Views PHP field and sorting by custom Views handler?

Question

In order to address some Views performance issues and respect best practices, I'd like to replace some Views PHP I configured some time ago by my own custom handlers.

For instance I've got a Views PHP field, excluded from display, with that setup :

Value code :

if( $row->sticky ==1 ) {
  return 100;
} else {

  if ( isset($row->product_id) && $row->product_id != ""  ){

    $query = "SELECT COUNT(statut.entity_id) FROM field_data_field_statut_depart statut"
    . " INNER JOIN  field_data_field_product product ON statut.entity_id= product.field_product_product_id"
    . " INNER JOIN  field_data_field_date_depart depart ON statut.entity_id = depart.entity_id"
    . " WHERE product.entity_id = ". $row->nid." AND field_statut_depart_value IN (2,3) AND field_date_depart_value > NOW(); ";

    $select = db_query($query);
    $count = $select->fetchField();

    return $count; 
  }
  else {
    return -1;
  }
}

Output code :

<?php print $value ; ?>`

Then I use that field as first sort criteria (ascending), in a Global PHP sort criteria:

if ($row1->php> $row2->php) return -1; else return 1;

I'd be really thankful if you could put me on the right way: in which function(s) shall I build that same code to end up with PHP in database ?

Summary :

After search and progress, plus @Renrahf help, most of the implementation seems ok, detailed below. But I'm still fighting with one point : I added a custom field handler to calculate a value, but how can I order by that handler ?

Edits :

What I did so far :

.info file

files[] = views_handler_vts_products_sort.inc
files[] = includes/views_handler_vts_count_depconf_field.inc

Module file

/**
 * Implements hook_views_data().
 */
function vts_views_handler_views_data() {
  $data['custom']['table']['group'] = t('Custom');
  $data['custom']['table']['join'] = array(
    // #global is a special flag which let's a table appear all the time.
    '#global' => array(),
  );

  $data['custom']['custom_handler'] = array(
    'title' => t('Vts custom Sort Handler'),
    'help' => 'Sorts products by sticky first then by custom statut field',
    'sort' => array(
      'handler' => 'views_handler_vts_products_sort',
    ),
  );

  $data['custom']['count_depconf_field'] = array(
    'title' => t('Sum of products with status confirmed '),
    'help' => t('Calculate Sum of products with status confirmed, to order lists".'),
    'field' => array(
      'handler' => 'views_handler_vts_count_depconf_field',
      'click sortable'=> TRUE,
    ),
    /*'sort' => array(
      'handler' => 'views_handler_sort',
    ), */
  );  
  return $data;
}

function vts_views_handler_views_api() {
    return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'vts_views_handler'),
  );
}

views_handler_vts_products_sort file

/**
 * Base sort handler that has no options and performs a simple sort.
 *
 * @ingroup views_sort_handlers
 */
class views_handler_vts_products_sort extends views_handler_sort {

  function query() {
    $this->ensure_my_table();
    // Add the field.
    $this->query->add_orderby('node', 'sticky', 'DESC');
  }
}

views_handler_vts_count_depconf_field file

/*
 * A simple field to calculate the value I wish to order by.
 */
class views_handler_vts_count_depconf_field extends views_handler_field {

  function query() {
    //do nothing
  }

  function render($values) {
    $count = 0;

    $product_id = isset($values-> commerce_product_field_data_field_product_product_id)? $values-> commerce_product_field_data_field_product_product_id: NULL;
    if(!is_null($product_id)){

      $query = "SELECT COUNT(statut.entity_id) FROM field_data_field_statut_depart statut"
      . " INNER JOIN  field_data_field_product product ON statut.entity_id= product.field_product_product_id"
      . " INNER JOIN  field_data_field_date_depart depart ON statut.entity_id = depart.entity_id"
      . " WHERE product.entity_id = " . $values->nid . " AND field_statut_depart_value IN (2,3) AND field_date_depart_value > NOW(); ";

      $select = db_query($query);
      $count = $select->fetchField();
    }
    return $count;
  }
}

Remaining question :

• how to order by the custom field handler ? I tried adding 'click sortable'=> TRUE, OR 'sort' => array('handler' => 'views_handler_sort',), OR $this->query->add_orderby('custom', 'count_depconf_field', 'DESC'); into custom sort handler. None works but return Unknown column in 'order clause'

• DONE : How can I get $row->product_id and $row->nid inside query() ? I need it to build the subquery. : Added a views handler field and found the row values in render($values)...

• DONE : Which part of the example handler do I have to edit ? The query function only ? Do I need to keep the whole example code or just the custom parts ?

Thank you

Answer 1

You need to use a views sort handler : https://api.drupal.org/api/views/handlers!views_handler_sort.inc/group/views_sort_handlers/7.x-3.x

You can't use PHP for sorting your results for performance reasons. PHP can only be used to sort results if you fetch the entire table results, and that's not an option most of the time.

So, you need to create your own view sort handler, configure it in your view and then use the views API functions to make the proper joins, where, maybe even subqueries to reach the data you need for your sort. In your case, a number of entities having particular date and type conditions.

All this code has to reside in the "query()" method of your object. You need to achieve a query like this :

SELECT table_x.field_y, ...
FROM  ...
...
...
ORDER BY row.sticky, (SELECT COUNT(statut.entity_id) 
FROM field_data_field_statut_depart statut
INNER JOIN field_data_field_product product
INNER JOIN field_data_field_date_depart depart
WHERE product.entity_id = table_x.field_y
AND field_statut_depart_value IN (2,3) 
AND field_date_depart_value > NOW())

By using the function https://api.drupal.org/api/views/plugins%21views_plugin_query_default.inc/function/views_plugin_query_default%3A%3Aadd_orderby/7.x-3.x and a subquery.

The subquery may be optimized in 3 or more joints and some where conditions maybe but I can't tell without the whole query.

EDIT

You extends from "views_handler" object but you should directly extends from "views_handler_sort" to be able to use the maximum of core default code :

class views_handler_vts_products_sort extends views_handler_sort {
  /**
   * Called to add the sort to a query.
   */
  function query() {
    $this->ensure_my_table();
    // Add the field.
    $this->query->add_orderby($this->table_alias, $this->real_field, $this->options['order']);
  }
}

As you can see above, only the "query" method is needed in your case as you don't need any specific configurations in the UI etc.

To get the product_id or nid inside your "query()" method, you have to use existing fields that were added to the query by views field handlers (and defined in your views UI).

This file is the perfect example of what you want to achieve (you can find it in the views documentation, it is an existing one but I'm not allowed to set the link as my reputation is too low) :

class views_handler_sort_node_version_count extends views_handler_sort {
  function query() {
    $this->ensure_my_table();

    $this->query->add_orderby(NULL, '(SELECT COUNT(vid) FROM {node_revision} WHERE nid = {' . $this->table_alias . '}.nid)', $this->options['order'], 'sort_node_version_count');
  }
}

See if you can adapt this code to your need and I'll be glad to see the end result :)

Answer 2

I share below the full implementation on how I did to replace Views PHP sorting by a custom Views handler.

.info file

files[] = includes/views_handler_my_custom_sort.inc

Module file

/**
 * Implements hook_views_data().
 */
function MODULE_NAME_views_data() {
  $data['custom']['table']['group'] = t('Custom');
  $data['custom']['table']['join'] = array(
    '#global' => array(),
  );

  $data['custom']['custom_handler'] = array(
    'title' => t('My custom Sort Handler'),
    'help' => 'Sorts products by sticky first then by custom statut field',
    'sort' => array(
      'handler' => 'views_handler_vts_products_sort',
    ),
  );

  return $data;
}

function MODULE_NAME_views_api() {
    return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'MODULE_NAME'),
  );
}

views_handler_my_custom_sort.inc file

/**
 * Base sort handler that has no options and performs a simple sort.
 *
 * @ingroup views_sort_handlers
 */
class views_handler_my_custom_sort extends views_handler_sort {

  function query() {
    $this->ensure_my_table();

    $sub_query = "(SELECT COUNT(p.field_product_product_id) "
      . "FROM field_data_field_product p "
      . "LEFT JOIN field_data_field_statut_depart statut ON statut.entity_id = p.field_product_product_id "
      . "LEFT JOIN field_data_field_date_depart depart ON depart.entity_id = p.field_product_product_id  "
      . "LEFT JOIN node nod ON nod.nid = p.entity_id "
      . "WHERE nod.nid = node.nid "//This is a the obligatory condition mapping the subquery with the outer query
      . "AND field_statut_depart_value IN (2,3) "
      . "AND field_date_depart_value > NOW())";

    /* I'm timeless to write the query with the object syntax, here was a beginning
    $sub_query = db_select('field_data_field_product', 'p');
    $sub_query->addField('p', 'field_product_product_id');
    $sub_query->leftJoin('node', 'nod', 'nod.nid = p.entity_id');
    $sub_query->where("nod.nid = node.nid");
    $sub_query->countQuery(); */  

    $this->query->add_orderby('node', 'sticky', 'DESC');
    $this->query->add_orderby(NULL, $sub_query, 'DESC', 'subquery');

  }
}

A bit of explanation : after understanding how to implement Views handlers, I got confused with the subquery :

• map it with the outer query to get a dynamic "row by" result : same table and column but different alias : WHERE nod.nid = node.nid
• set the alias in add_orderby : $this->query->add_orderby(NULL, $sub_query, 'DESC', 'subquery'); works, but $this->query->add_orderby(NULL, $sub_query, 'DESC'); doesn't

This last point was surprising because while SELECT TITLE FROM node ORDER BY (SELECT COUNT(field_product_product_id) FROM field_data_field_product p LEFT JOIN node nod ON nod.nid = p.entity_id WHERE nod.nid = node.nid ) works in SQL direct input, it doesn't across current setup.

You need to specify the subquery alias and the final query will be something like SELECT TITLE, (SELECT COUNT(field_product_product_id) FROM field_data_field_product p LEFT JOIN node nod ON nod.nid = p.entity_id WHERE nod.nid = node.nid ) as subquery FROM node ORDER BY subquery

The tries to calculate the values to sort the result in a custom handler field, did not work because Views sorting is done on a DB basis and the custom field handler is kind of dummy field... at least this was my conclusion.