Making a custom views field handler click sortable (getting an error when clicked)

Question

When I make my custom handler click sortable and subsequently click the label in the table heading of the view I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'unknown' in 'order clause'

My handler is defined as below. This is a render only handler that performs a calculation on some other field data and outputs.

UPDATE:

Am I right in thinking that because this data is not stored in the database that I can't make it click sortable because there is no column to order by in the SQL statement that is generated?

Is there a way around this? Any help appreciated.

function rate_calc_views_data() {

$data = array();

$data['node']['rate_calc_handler_result'] = array(
    'title' => t('Standard Deviation'),
    'help' => t('Calculates the voting result standard devaition'),
    'field' => array(
      'handler' => 'rate_calc_handler_result',
      'click sortable' => TRUE,
    ),
    'sort' => array(
       'handler' => 'views_handler_sort',
     ),
     'filter' => array(
       'handler' => 'views_handler_filter',
     ),
);  return $data; }

Answer 1

This is old, but there is some misinformation here so I thought I'd contribute.

Make sure you expose your pseudo field to Views. Don't overdo this and confuse yourself, just add what is required. The handler doesn't need to be defined in here cause you can use annotations.

/**
 * Implements hook_views_data_alter().
 */
function hook_views_data_alter(array &$data) {
  $data['shift']['shift_time'] = array(
    'title' => t('Total Shift Time'),
    'field' => array(
      'title' => t('Total Shift Time'),
      'help' => t('Calculates a shift length.'),
      'id' => 'shift_time',
    ),
  );
}

My field class is defined like so, note the annotations.

/**
 * Field handler for pseudo shift time field.
 *
 * @ingroup views_field_handlers
 *
 * @ViewsField("shift_time")
 */
class ShiftTime extends FieldPluginBase {

Then, in my field class ShiftTime I have a clickSort method (NOT click_sort - must be camelCase!);

function clickSort($order) {
  $alias = $this->query->addField(NULL, "(finish_time - start_time)", 'shift_time');
  $this->query->addOrderBy(NULL, NULL, $order, $alias);
}

As you can see you can simply write an expression or formula as a field and then sort by that. If you need to you can write an entire sub query.

Some API links;
addOrderBy()
addField()

Answer 2

Because this data is not stored in the database (it's just a dummy field that returns the result of some calculations) you can't make it click sortable as there is no database column to 'order by' in the SQL statement generated by the view.

There must be a way around this but it's likely quite complicated and beyond me at the moment.

If you have a custom handler that does something to change the way you display existing field data you can make it click sortable by overriding the click_sort($order) function in your handler class.

My mymodule.views.inc now looks like the below (where 'real field' is the name of the field included in the view, before the custom field created by my handler, but excluded from display)

<?php 

function mymodule_views_data() {

    $data = array();

    $data['node']['mymodule_handler_myname'] = array(
        'title' => t('Score'),
        'help' => t('Makes score values human readable'),
        'real field' => 'value',
        'field' => array(
          'handler' => 'mymodule_handler_myname',
          'click sortable' => TRUE,
        ),
    ); 

    return $data;  

}

?>

and my mymodule_handler_myname.inc looks like

<?php

class mymodule_handler_myname extends views_handler_field {

    /**
    * Loads additional fields.
    */
    function query() {

    }  

    function click_sort($order) {

        $this->query->orderby[] = array(
            'field' => 'field_name', // here may be a field, or some SQL clause e.g. FIELD(str, str1, ...)
            'direction' => strtoupper($order),
        );

    }

    /**
    * Renders the field handler.
    */
    function render($values) {       

        if (isset($values->field_name)){

            switch ($values->field_name){
                case 2:
                    $vote = 'Yes';
                    break;
                case 0:
                    $vote = 'No';
                    break;
                default:
                    $vote = 'Maybe';
                    break;
            }

            return t($vote);

        }
    }
}

?>

This wrecked my head for a full day so hopefully it's helpful to someone in the future.