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

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 :)

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.