1

I need a status field to appear in a view with a table format that has values like "Needs Attention", "Awaiting Payment", "Not Interested", etc...

These values are based on values of other fields attached to the node such as a date field indicating the last time of interaction, payment received toggle, etc...

This field needs to be sortable and filterable for good reporting; so, I've started reading-up on the Views API to create custom Views Data, but I'm having trouble figuring-out where to attach the SQL expression that calculates the value. I've seen some tutorials that are close, but they talk more about getting your custom SQL table into views and using expressions on those, but not one working with existing node field values.

Does anyone know how and where to set this up, or has anyone found something on the web describing this?

1

I had started in the right direction this morning Thanks to this article: Drupal 7: Using SQL Expressions in Views 3

This afternoon, after some digging, I found that you can manually join the tables that you need to work with in the same handler as mentioned in the article, and this expression field can be added to the "views" index of the data variable, and it will still be added to the base query.

So in, mymodule.views.inc

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

    $data['views']['student_status'] = array(
        'group' => 'Content',
        'title' => t('Student Status'),
        'help' => t('The current status of a student based on when they we last interacted with, paid, etc...'),
        'field' => array(
          'handler' => 'mymodule_views_handler_field_expression',
          'click sortable' => TRUE,
          'expression' => "                                         
                CASE
                    WHEN
                        field_data_field_some_table.field_some_value = 1
                    THEN 'Registered'

                    WHEN
                         DATEDIFF(NOW(), field_data_field_last_interaction_date.field_last_interaction_date_value) > 30
                    THEN 'Needs Attention'          

                    ELSE 'Unregistered'
                END             
            ",
      'expression_alias' => 'student_status',
    ),
   'filter' => array(
     'handler' => 'mymodule_views_handler_filter',
     'allow empty' => TRUE,
   ),
    'argument' => array(
      'handler' => 'mymodule_views_handler_argument',
     'allow empty' => TRUE,
    ),
   'sort' => array(
     'handler' => 'views_handler_sort',
   ),
  );

 return $data;
}

And then in mymodule_views_handler_field_expression.inc

class mymodule_views_handler_field_expression extends views_handler_field {
  var $expression_alias = 'unknown';
  var $expression = NULL;
  /**
   * Construct a new field handler.
   */
  function construct() {
    parent::construct();
    if (!empty($this->definition['expression'])) {
      $this->expression = $this->definition['expression'];
    }
    if ( !empty($this->definition['expression_alias']) ) {
      $this->expression_alias = $this->definition['expression_alias'];
    }
  }

  /**
   * Called to add the expression as a field to a query.
   * Overrides and based on views_handler_field->query().
   */
  function query() {
    // Make sure the table is available, and get the alias.
    $table_alias = $this->ensure_my_table();

    // Boilerplate from views_handler_field->query().
    $params = $this->options['group_type'] != 'group' ? array('function' => $this->options['group_type']) : array();

    //HERE'S THE NEW STUFF
    $this->query->add_table('field_data_field_last_interaction_date');
    $this->query->add_table('field_data_field_some_table');

    // Insert the table alias into the expression and its alias, and use the add_field method of the underlying query object.
    // See add_field() in views_plugin_query_default.inc
    $this->field_alias = $this->query->add_field(NULL, $this->expression, $this->expression_alias, $params);

    // More boilerplate.
    $this->add_additional_fields();
 }

}

ALSO NOTE: In order to use this custom field in the Context Filter and Filter Criteria, I had to create custom argument and filter handlers to replace the WHERE statement with a HAVING statement, as well as, clean-up the alias that it uses.

Argument

class mymodule_views_handler_argument extends views_handler_argument {
     /**
   * Set up the query for this argument.
   *
   * The argument sent may be found at $this->argument.
   */
  function query($group_by = FALSE) {
    $this->ensure_my_table();
    $this->query->add_having('Content', $this->real_field, $this->argument);
  }
}

Filter

class mymodule_views_handler_filter extends views_handler_filter_string {

     /**
   * Add this filter to the query.
   *
   * Due to the nature of fapi, the value and the operator have an unintended
   * level of indirection. You will find them in $this->operator
   * and $this->value respectively.
   */
  function query() {
    $this->ensure_my_table();
    $field = $this->real_field;

    $info = $this->operators();
    if (!empty($info[$this->operator]['method'])) {
      $this->{$info[$this->operator]['method']}($field);
    }
  }

  function op_equal($field) {
    $this->query->add_having($this->options['group'], $field, $this->value, $this->operator());
  }

  function op_contains($field) {
    $this->query->add_having($this->options['group'], $field, '%' . db_like($this->value) . '%', 'LIKE');
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.