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:
- There is a content type as 'Products'.
- The 'Product' content type has a field called 'ISBN'
- The 'ISBN' field is referenced in a custom table called 'activation_code'
- Each ISBN is activated using the activation code and there are multiple entries of activation codes per ISBN.
- 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
- Custom Table: activation_code
- 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