Drupal 8: Creating A Subquery In Views

You've probably been there before. A Drupal View you are working on looks great and has all the data and fields you need, but when you look a little closer at the results you realise that something is off. After looking at the generated SQL query you see that there is a problem with one of the joins which causes your counts to be off a little. Ultimately, you need to remove this join, but you really need that data included in your results.

Creating a subquery can allow you to pull out data from a specific field without adding another join that would cause the results to be out.

I had this problem in a View I was working on the other day that incorporated data from the Download Count module. This module records the action of a user downloading a file on your site. Whilst it does come with some Views integration, I had a couple of other relationships added that caused the download count to be incorrect. After some investigation I knew that I needed to create a subquery to inject the value of the download count into the output of the View.

The best way to do this is to create a View field handler that will create a subquery as part of the field setup. To create a field handler plugin you need to create a class at the location my_module/src/Plugin/views/field. The following is a class called FileDownloadCount that extends the default NumericField Views field. It's the annotation at the class level that let's Views know about this field, so that bit is quite important. The full file path to my plugin is my_module/src/Plugin/views/field/FileDownloadCount.php.

<?php

namespace Drupal\my_module\Plugin\views\field;

use Drupal\views\Plugin\views\field\NumericField;
use Drupal\views\ResultRow;

/**
 * Field handler for search score.
 *
 * @ingroup views_field_handlers
 *
 * @ViewsField("file_download_count")
 */
class FileDownloadCount extends NumericField {

}

This is picked up automatically by Views, but it won't do anything until we add some more methods to it.

The first thing we need to add is a query() method so that Views knows about the subquery and can add the needed field to the display output. A subquery is created by creating a new instance of a database query and then adding this as a join to the main query as if it was a real table. The subquery here (because of the data I am getting) is dependent on the file_managed table, so this form the basis of the join we create.

Here is the method in full, with comments to help break down what is going on.

/**
 * {@inheritdoc}
 */
public function query() {
  // Add a subquery to the query that will find the download count.
  $subQuery = \Drupal::database()->select('download_count', 'download_count');
  $subQuery->addField('download_count', 'fid');
  $subQuery->addExpression("COUNT(fid)", 'file_download_count');
  $subQuery->groupBy("download_count.fid");

  // Add the subquery to as a component of a join.
  $joinDefinition = [
    'table formula' => $subQuery,
    'field' => 'fid',
    'left_table' => 'file_managed',
    'left_field' => 'fid',
    'adjust' => TRUE,
  ];

  // Create a join object and create a relationship between the main query and the subquery.
  $join = \Drupal::service('plugin.manager.views.join')->createInstance('standard', $joinDefinition);
  $this->query->addRelationship('download_data', $join, 'file_managed');
  
  // Add the field to the Views interface.
  $this->query->addField(NULL, 'file_download_count', 'file_download_count');
}

We now have the subquery in place and the field added to the main query, so the data we need will now pull through. A nice thing to have on top of this is a default value for when the output of the query is null. This will be returned when a file was never downloaded and so will happen quite regularly at the start of the project, and will also make it difficult for this feature to past testing. To rectify this we just need to detect if the value is null and reset it to be 0 using the render() method.

/**
 * {@inheritdoc}
 */
public function render(ResultRow $values) {
  if (is_null($values->file_download_count)) {
    // Ensure a null value is printed as a 0.
    $values->file_download_count = 0;
  }
  return parent::render($values);
}

The render is called by Views automatically as the output is rendered.

Finally, it's a good idea to also allow your new field to have a click sort ability, which is especially important if you are creating a table as allowing the sorting of columns is a really good feature. To do this you need to create a method called clickSort() that calls the addOderBy() method on the query. Views will automatically call this method when it needs to sort the View by your field.

/**
 * {@inheritdoc}
 */
public function clickSort($order) {
  if (isset($this->field_alias)) {
    $params = $this->options['group_type'] != 'group' ? ['function' => $this->options['group_type']] : [];
    $this->query->addOrderBy(NULL, 'file_download_count', $order, $this->field_alias, $params);
  }
}

With everything in place we just need to add this field to a View like a normal field. You will also need to configure the sort in the Views table output to get the sort working as well.

I have seen a couple of posts on the internet showing how to create a subquery in Views, but they tend to miss the key item of also adding the data to the output of the View. The above solution will add the subquery and add the data to your view so you can do something with it.

Comments

Amazing, what a gem. 

Permalink

Thank you so much for this.

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
3 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.