If you ever need to output Drupal Commerce orders or order information in a View, DC luckily comes with a plethora of fields and relationships that can get almost any data output required. But there is one type of relationship missing: relating an order to one of the order's line items based on specific conditions. For example, let's say you want to get the line item with type Tax for an order. If you are only showing one line-item type in the View, you could solve the problem with the Views filter criteria. But what if you need to get multiple line-item relationships for various types (e.g., Tax, Shipping, Handling)? In this case you'll need to create a custom Views relationship. Thankfully, it's fairly straightforward.

For this example module, we want to build a report. The report needs to show a list of orders and there needs to be a column for showing the Tax line item total.

First, create the View. For this example, I'm making a page that will show Commerce Order of type All sorted by Unsorted. The Display format will be a Table of fields. Add whatever field data you want to this View for testing.

Next, create your module. For this example, I'm going to create a new module folder called tag_reports in which we will store a number of files. I'll go ahead and list all the files we'll be making.

Note: The naming convention matters! The Views module looks for specific file endings, so it is important to name your files correctly.

1. tag_reports.info
2. tag_reports.module
3. tag_reports.views.inc
4. handlers/tag_reports_handler_relationship_tax_line_item_representative.inc

Note that the last file is in a folder called handlers within the module folder.

For tag_reports.info, we'll do a typical Drupal module info file and we'll include a reference to our handler file so that is loaded and available for reference.

name = TAG Reports
description = Custom report relationship for TAG.
core = 7.x
package = TAG
dependencies[] = views
files[] = handlers/tag_reports_handler_relationship_tax_line_item_representative.inc

For tag_reports.module, all we need to do is register with the Views API.

/**
* Implements hook_views_api().
*/
function tag_reports_views_api($module = NULL, $api = NULL) {
  return array("api" => "3.0");
}

For tag_reports.views.inc, we will register our custom commerce_order relationship. We add on to the existing commerce_order Views data to define an additional relationship that should be available. We then provide the handler class that will perform the relationship logic and then define how the underlying table schema will be joined.

/**
* Implements hook_views_data_alter().
*/
function tag_reports_views_data_alter(&$data) {
  // Add a relationship to the order table to join to a representative tax
  // line item (in this case, the one with the lowest line item ID) through the
  // commerce_line_items field data table.
  $data['commerce_order']['tax_line_item_representative']['relationship'] = array(
    'title' => t('Representative tax line item'),
    'label' => t('Representative tax line item'),
    'help' => t('Obtains a single representative tax line item for each order.'),
    // This defines the file and class that will be instanced and used by Views for the relationship.
    'handler' => 'tag_reports_handler_relationship_tax_line_item_representative',
    // This is the name of the table we are going to be joining to.
    'base' => 'commerce_line_item',
    // This is the field on the joined table we'll be using.
    'base field' => 'line_item_id',
    // This is the name of the table we are going to be joining from.
    'relationship table' => 'commerce_order',
    // This is the field on the relationship table we'll be using for joining.
    'relationship field' => 'commerce_order.order_id',
  );
}

Now that we have everything registered, we can write the code that does the actual relationship logic in tag_reports_handler_relationship_tax_line_item_representative.inc in the handlers folder. I've provided some comments in the code to help understand what's going on. Basically, what we are doing is adding some SQL that does the relationship JOIN logic.

/**
* Defines a relationship for an order to a representative line item.
*/
class tag_reports_handler_relationship_tax_line_item_representative extends views_handler_relationship {
  /**
  * Some boilerplate code for showing the Views label.
  */
  function label() {
    if (!isset($this->options['label'])) {
      return $this->ui_name();
    }
    return $this->options['label'];
  }
  /**
  * Provide the logic for creating and performing the relationship.
  */
  function query() {
    // Get the JOIN type from the relationship settings.
    // This is "required" checkbox when creating a relationship on a View.
    $join_type = empty($this->options['required']) ? 'LEFT' : 'INNER';
    // This function populates $this->table_alias with the proper query alias to
    // the order table.
    $this->ensure_my_table();
    // Provide a definition for the relationship.
    $definition = array(
      'table' => 'commerce_line_item',
      'field' => 'line_item_id',
      'left_table' => $this->table_alias,
      'left_field' => 'order_id',
      // Add the join SQL. We want to get the first line item id (via MIN) that
      // is attached to an order and we want to make sure the line item type
      // is "tax".
      'left_query' => "
        SELECT MIN(subcli.line_item_id) as min_line_item_id
        FROM field_data_commerce_line_items subfdcli
        LEFT JOIN commerce_line_item subcli
        ON subfdcli.commerce_line_items_line_item_id = subcli.line_item_id
        WHERE subfdcli.entity_id = {$this->table_alias}.order_id
        AND subcli.type = 'tax'
        ORDER BY subcli.line_item_id",
      'type' => $join_type,
    );
    // Create a new subquery Views object and attach our definition.
    $join = new views_join_subquery();
    $join->definition = $definition;
    $join->construct();
    $join->adjusted = TRUE;
    // Add the relationship to the query.
    $alias = 'tag_reports_tax_line_item_representative';
    $this->alias = $this->query->add_relationship($alias, $join, 'commerce_line_item', $this->relationship);
  }
}

Once all of this is in place, enable the module and clear the Drupal cache, then refresh the Views edit page we created.

On the Views edit page, click the Advanced header if that section is closed and then click Add next to Relationships. The new relationship type should be available for use. Filter by Commerce Order if you have trouble finding it. If it doesn't appear, ensure that the Drupal cache was cleared and that the new module was enabled successfully. Click the checkbox next to the relationship, then click Add and configure relationships. Click Apply on the next screen and the relationship will be added. At this point you can now add fields to the table that use the new relationship.

And that's it! With a little extra work you could make the Tax type configurable to be a form field on the View relationship settings popup if you need to do multiple types on the same view.

I hope I've been able to help you out. Let me know in the comments if you have any additional pointers or just need some help. Thanks!