Replace views PHP field by views handlers - not working

Question

I am migrating a site to Drupal 7 from Drupal 6 and came across a view which had custom code in Views PHP. The logic is:

1. There is a content type as 'Products'.
2. The 'Product' content type has a field called 'ISBN'
3. The 'ISBN' field is referenced in a custom table called 'activation_code'
4. Each ISBN is activated using the activation code and there are multiple entries of activation codes per ISBN.
5. In the Views the fields which are displayed are 'ISBN' (coming from Content type Product), total number of activation codes per ISBN (coming from views php with each row's ISBN), activated activation codes (same as previous one).

I have seen a post with kind of similar implementation here: How to replace Views PHP field and sorting by custom Views handler?

My implementation is the following.

activation_code.module

/**
 * Implements hook_views_api().
 */
function clms_product_activation_views_api() {
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'activation_code') . '/includes/views',
  );
}

activation_code\includes\views\activation_code.views.inc

function activation_code_views_data() {
  $data = array();

  $data['manage_products']['table']['group'] = t('Manage Product View');
  $data['manage_products']['table']['join'] = array(
    '#global' => array(),
  );
  $data['manage_products']['total_subscriptions'] = array(
    'title' => t('Total Subscriptions'),
    'help' => t('Total Subscriptions.'),
    'field' => array(
      'handler' => 'activation_code_total_subscriptions_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'activation_code_total_subscriptions_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
  );


  return $data;
}

The field handler is placed in the activation_code\includes\views\handlers directory and it contains this code.

/**
 * @file
 * Contains the custom field data for total subscriptions.
 */

/**
 * Custom views handler for manage product views.
 */
class activation_code_total_subscriptions_field extends views_handler_field {

  /**
   * {@inheritdoc}
   */
  function query() {
    // Do nothing.
  }

  /**
   * {@inheritdoc}
   */
  function render($values) {
    $result = db_select('activation_code', 'ac')->fields('ac', array('nid'))
      ->condition('field_isbn', $values->field_field_isbn[0]['raw']['safe_value'], '=')
      ->execute();
    return $result->rowCount();
  }

}

The data is coming properly but while I sort, it gives me error 'Unknown column unknown in SQL'. Which I found that custom handlers if not known throws this type of error.

I believe that the sort handler I have written (activation_code\includes\views\handlers\activation_code_total_subscriptions_sort.inc) is not getting called.

/**
 * @file
 * Contains custom sort function for Manage Products total subscription field.
 */

/**
 * Overriding views_handler_sort class.
 */
class activation_code_total_subscriptions_sort extends views_handler_sort {

  function query() {
    error_log('CALLED', 3, '/tmp/access.log');
    $this->ensure_my_table();
  }

}

I have mentioned this in .info file as such.

files[] = includes/views/handlers/activation_code_total_subscriptions_field.inc
files[] = includes/views/handlers/activation_code_total_subscriptions_sort.inc

Am I missing something? Even this gets called, what type of sort I can give to prevent the unknown column error?

Update #1 Based on Matt's suggestion, I have tried but can't get the row values in query() method. In $this->options I am only getting this:

array (
  'id' => 'total_subscriptions',
  'table' => 'clms_view_manage_products',
  'field' => 'total_subscriptions',
  'relationship' => 'none',
  'group_type' => 'group',
  'ui_name' => '',
  'label' => 'Total Subscriptions',
  'exclude' => 0,
  'alter' =>
  array (
    'alter_text' => 0,
    'text' => '',
    'make_link' => 0,
    'path' => '',
    'absolute' => 0,
    'external' => 0,
    'replace_spaces' => 0,
    'path_case' => 'none',
    'trim_whitespace' => 0,
    'alt' => '',
    'rel' => '',
    'link_class' => '',
    'prefix' => '',
    'suffix' => '',
    'target' => '',
    'nl2br' => 0,
    'max_length' => '',
    'word_boundary' => 1,
    'ellipsis' => 1,
    'more_link' => 0,
    'more_link_text' => '',
    'more_link_path' => '',
    'strip_tags' => 0,
    'trim' => 0,
    'preserve_tags' => '',
    'html' => 0,
  ),
  'element_type' => '',
  'element_class' => '',
  'element_label_type' => '',
  'element_label_class' => '',
  'element_label_colon' => 1,
  'element_wrapper_type' => '',
  'element_wrapper_class' => '',
  'element_default_classes' => 1,
  'empty' => '',
  'hide_empty' => 0,
  'empty_zero' => 0,
  'hide_alter_empty' => 1,
)

Update #2 - Table relationships & Use Case

Use Case: There is a 'Product' content type (node). When a product is created, an ISBN is added to a field attached to the 'Product' content type. The field is called here 'field_isbn'. So yes, 'field_isbn' is a field in the "product" bundle. For each product associated with one ISBN there are many activation codes. Thus, users who uses those activation code, gets access to that product with the ISBN. The activation codes are stored in 'activation_code' table. This is a custom table and the data comes from a different site.

Table Relationships

1. Custom Table: activation_code
2. Node field table: field_data_field_isbn

The table schema is here: https://gist.github.com/aneek/a748f87205b1a0dace1db57998d2be80

NOTE: There is a 'nid' column in activation_codes table, but that is not a node id. So there is no relation with node table.

The relation is between field_data_field_isbn.field_isbn_value and activation_code.field_product_isbn_value. It's one to many relation.

In the old view the View's PHP module use to run this below:

$total_subscriptions = db_result(db_query("SELECT COUNT(`nid`) AS total_subscriptions FROM {activation_code} WHERE `field_product_isbn_value` = '%s'",  $data->node_data_field_isbn_field_isbn_value));
print $total_subscriptions;

In the new view I am trying to do the same but in query() method. Views generates the below query:

SELECT node.nid AS nid, node.title AS node_title, (SELECT COUNT(cac.nid) FROM {activation_code} cac
WHERE cac.field_product_isbn_value = 9781107875838) AS field_total_subscriptions, 'node' AS field_data_field_isbn_node_entity_type, 'node' AS field_data_field_product_status_node_entity_type
FROM 
node node
WHERE (( (node.type IN  ('product')) ))
ORDER BY node_title ASC
LIMIT 25 OFFSET 0

I have written a dummy ISBN here as I need to replace this with a proper variable in the custom field handler. This is where I am currently stuck.

Update #3 The total code snippet (I am providing one handler and removing other two as they are similar.)

product_activation.views.inc

<?php

/**
 * @file
 * Contains the views hooks and alters.
 */

/**
 * Implements hook_views_data().
 */
function product_activation_views_data() {
  $data = array();

  $data['clms_view_manage_products']['table']['group'] = t('Manage Product View');
  $data['clms_view_manage_products']['table']['join'] = array(
    '#global' => array(),
  );
  $data['clms_view_manage_products']['total_subscriptions'] = array(
    'title' => t('Total Subscriptions'),
    'help' => t('Total Subscriptions.'),
    'field' => array(
      'handler' => 'views_handler_product_activation_total_subscriptions_field',
      'click sortable' => TRUE,
    ),
  );
  return $data;
}

views_handler_product_activation_total_subscriptions_field.inc

<?php

/**
 * @file
 * Contains the custom field data for total subscriptions.
 */

/**
 * Custom views handler for manage product views.
 */
class views_handler_product_activation_total_subscriptions_field extends views_handler_field {

  /**
   * {@inheritdoc}
   */
  function query() {
    $isbn = db_query("SELECT field_isbn_value FROM {field_data_field_isbn} WHERE entity_id = node.nid")->fetchField();
    $sql = "SELECT COUNT(cac.nid) FROM {activation_code} cac WHERE cac.field_product_isbn_value = {$isbn}";
    $table = $this->ensure_my_table();
    $this->field_alias = $this->query->add_field(NULL, "($sql)", 'field_total_subscriptions');
  }

  /**
   * {@inheritdoc}
   */
  function render($values) {
    $output = '';
    $value = $this->get_value($values);
    //dsm($value);
    return empty($value) ? '' : $value;
  }
}

The error that I am getting is, 'Column not found; Unknown column 'node.nid' in where clause. So this has also failed. I am sure I am missing something.

The whole views export is here: https://gist.github.com/aneek/706f92a3c13b50a06e3a4e7cdce8679f

Answer 1

First off, I'm sure you already tried this: But if you are just doing a row count based on this one relationship, you should be able to do this with Views aggregation. Then you can sort of that. There are helper modules for this too.

For using a views handler you have everything setup pretty correctly. You do not however need to use a filter handler for this case. You are only trying to get click sort to work on a custom field, not create a custom filter field.

It looks like you are trying to sort total_subscriptions "field" however this is not truly a field, it is simply a rowCount() that you are rendering on each row. When click sorting, it is attempting to run a query to sort total_subscriptions, but when it queries that table, no such column exists. Try running a query and rendering the output into a field.

You will need to:

• convert your db_select to a sql string and put it in the query definition
• figure out how to access your field_field_isbn raw value inside $this query()
• attach the sql query to a field_alias on the table
• render the field in render($values)
• now, click sorting should automatically work, because the count value is held inside the query field

Here is an example of how I create a custom views field, counting all of the units for a project node, returned the units count into a field on the row, which allowed click sort to use. Note, we will not be adding a custom filter handler. Just a field handler with click sort.

ec_app.views.inc

<?php
/**
 * Implements hook_views_data_alter().
 */
function ec_app_views_data_alter(&$data) {
  $data['node']['field_unit_types_count'] = array(
    'title' => t('Unit Types Count'),
    'help' => t('Count nodes by selected term.'),
    'field' => array(
      'handler' => 'ec_app_handler_field_unit_types_count',
      'click sortable' => TRUE,
    ),
  );
 }

ec_app_handler_field_unit_types_count.inc

<?php

class ec_app_handler_field_unit_types_count extends views_handler_field {

    function query() {
        $tid = $this->options['term'];
        //for you, this needs to be the value of field_field_isbn

        //build your sql instead of db_select
        $sql = "SELECT COUNT(*) FROM {field_data_field_units} fu JOIN {field_data_field_unit_types} ut ON ut.entity_id = fu.field_units_value
          WHERE fu.entity_id = node.nid AND ut.entity_type = 'field_collection_item' AND ut.bundle = 'field_units' AND ut.deleted = 0 AND ut.field_unit_types_tid = " . $tid;

        $table = $this->ensure_my_table();
        $this->field_alias = $this->query->add_field(NULL, "($sql)", 'field_unit_types_count'); //this should be the name of your field defined in data_alter or data
    }

    function render($values) {
        $output = '';
        $value = $this->get_value($values);
        return empty($value) ? '' : $value;
    }
}

and that should be all you need to make click sort work for a custom field in views.

Keep in mind I am using ec_app_views_data_alter($data) instead of ec_app_views_data(). You can try my method while continueing to use hook_views_data(). Just try removing the sort and filter handlers. Just stick with 1 field handler and click sort = true.

UPDATE #2 ONE METHOD OF GETTING FIELD ISBN VALUE IN VIEWS HANDLER QUERY()

$isbn_query = db_query("SELECT field_isbn_value FROM {field_data_field_isbn} WHERE entity_id = node.nid");
$isbn_return = $isbn_query->fetchAll();
//dpm($isbn_return) //do this if you want to see the return. Only if you have devel module enabled

//you should only have 1 record so grab the first instance if its not empty
if (!empty($isbn_return[0]->field_isbn_value)) {
    $isbn_value = $isbn_return[0]->field_isbn_value;
} else {
    // do something different here because theres no isbn value.
    $isbn_value = 0;
}

Put this inside your query() function. This should get you the $isbn_value. You can then include $isbn_value in your next SQL statement that counts your records and adds the count to a field.

Note: you're probably asking yourself, how does it know which nid to select as the entity_id. All he has there is "entity_id = node.nid" which is not a php variable containing the node id of that row... When the view executes this query() it will plugin the true node id of that row.

If this doesn't work or if you need help writing the second sql statement, please post your entire views handler snippet for us to look at.

UPDATE #3 HOW TO GET NODE.NID IN views_handler_field::query

I think maybe you are failing at getting the node.nid because of how you are adding your custom field. Putting node.nid inside a sql query should definitely work. This is a guess, but try this:

Remove your hook_views_data and replace with hook_views_data_alter(). Then append your custom field to the ['node'] array.

activation_code.views.inc

/**
 * Implements hook_views_data_alter().
 */
function product_activation_views_data_alter(&$data) {

//    $data['clms_view_manage_products']['table']['group'] = t('Manage Product View');
//    $data['clms_view_manage_products']['table']['join'] = array(
//        '#global' => array(),
//    );
    $data['node']['clms_view_manage_products']['total_subscriptions'] = array(
        'title' => t('Total Subscriptions'),
        'help' => t('Total Subscriptions.'),
        'field' => array(
            'handler' => 'views_handler_product_activation_total_subscriptions_field',
            'click sortable' => TRUE,
        ),
    );
//    return $data;
}