Programmatically sorting a view by a field combination in Drupal 8

I ran into an interesting problem with Views the other day. You know – the kind of problem that you can solve in minutes with a custom database query, but don’t really want to, because it’d involve dealing with all sorts of complexity that some smart someones already made Views handle for you. Complexity like, well, actually manually writing database queries, caching their results, theming those results – that sort of thing.

Here’s my scenario: I had a content type that could either contain one or more downloadable files or contain no files at all. In the latter case, the item described by the node would instead have to be ordered by the user in whatever quantity they required. I also had a view that was supposed to sort these nodes by popularity – that is, either the number of downloads or the number of orders. One of those values would always be empty.

Explaining the methods I used for storing the number of orders and downloads would require a separate blog post and might be a bit too far in the realm of “your mileage may vary” to be of much general use. Suffice to say that a private file system was needed and various hooks had to be implemented: hook_file_download for counting file downloads, hook_cron for periodically updating them to their parent media entities (and the media entities’ parent nodes) and a webform submission presave hook to count orders. To save time, I opted to store the order and download counts in two integer fields in the content type, so I wouldn’t have to deal with my own database table for them.

I had inherited a view that rendered these nodes using a special view mode and would just need the popularity sorting added in. Personally, I prefer field-based views, as I feel they give you more control over what gets grabbed from the database and how. When discussing this, though, I’m pretty sure I could hear the front-end developers gnashing their teeth, so I decided not to press the issue and didn’t touch the view. That, unfortunately, meant I didn’t have the luxury of getting new table joins in my database query just by adding a couple of invisible fields in the Views UI.

The first order of business, then, was adding some fields to the query that could be used for sorting the results. When you’re rendering full nodes in Views, the query it makes doesn’t contain the necessary join operations for you to get field data for specific fields in the node. I had to manually join the data tables for both of my counter fields into the query:

$join = Drupal::service('plugin.manager.views.join'); // Add relationship to download count field table. $query->addRelationship( 'download_data', $join->createInstance( 'standard', [ 'table' => 'node__field_download_count', 'field' => 'entity_id', 'left_table' => 'node_field_data', 'left_field' => 'nid', ] ), 'node_field_data' );

This should go in a hook_views_query_alter implementation in the module_name.views_execution.inc of your custom module. If you put it anywhere else, it won’t get run every time the view executes. Don’t do that.

What this does is it joins the node_field_data and node__field_download_count tables using an inner join, so you can fetch values from both tables into your result set without your database throwing a hissy fit. This second table we call download_data (the first argument given to addRelationship). The same is done for the order count field; only the table and field names change, so I don’t include the code here.

Next, you need to fetch some actual columns from your newly joined tables. Let Views do its own thing and just worry about the things you need to sort the results by:

 // Add the download count field.
$query->addField(
'download_data',
'field_download_count_value',
'field_download_count_value'
);

The code above makes your query fetch the field_download_count_value field from the download_data table (which is what we named our relationship, above). Do the same for the order count field (again, simply change the table and field names), and we end up with a result set that’s basically the node’s ID (and whatever else Views happens to fetch by itself) and the values of its download and order count fields:

node_field_data.nid as nid
download_data.field_download_count_value as field_download_count_value
order_data.field_order_count_value as field_order_count_value

So, now we have two extra fields, order and download count. One of them will always be empty for every node, so they’re of little use for sorting in themselves. Being standard fields, they’d also be automatically available in the Views UI to be used for sorting, but that sort will always be of the “first sort by field A, then sort by field B” variety, and one of the fields always being 0 makes it pointless. In order to properly determine the popularity of a node, we want the value of the greater of these two number fields. We can use built-in database functions for that, but to make that work with Views, we need to add another new field:

// Add a magic popularity field. The value is either the download or the
// order count, whichever is greater (or 0 if neither have a value).
$query->addField(
NULL,
'GREATEST(COALESCE(field_download_count_value, 0), COALESCE(field_order_count_value, 0))',
'material_popularity'
);

This adds a new field into the query that’s not related to any of our tables (hence the first NULL argument); in fact, it’s an expression, not a field. This expression uses two common database functions, GREATEST (returns the largest value in a given list, or null if any value is null) and COALESCE (returns the first value in a given list that is not null). The result of each COALESCE is either 0 or the value of the given field (if greater than 0), and the value of GREATEST is the larger of the two COALESCEs. Simple, right? All that remains is sorting the view results based on the value of this expression:

// Order the view by our new popularity field.
$query->addOrderBy(
NULL,
NULL,
'desc',
'material_popularity'
);

That’s it! The view will now be sorted properly based on the popularity field. Something to keep in mind is that your fancy new sort won’t be visible in the Views UI at all, so adding a comment in the view about your alter hook might be prudent. If you want the sort to be usable in the Views UI – maybe even with configurable fields – you’ll have to create a Views plugin out of it. That, however, is an exercise for another day.

Thoughts by