How to Sort Views results based on two fields

Question

I have a view that lists nodes of type Article. Each Article includes a boolean field and a date field. Neither field is required. I want to sort the list of articles so that the first articles are the ones with the boolean field checked and with a date that is in the future (sorted by published date – newest first) followed by all the other articles sorted by published date (newest first) (which includes ones without the boolean checked and ones that have the boolean checked but the date is in the past). The list has a pager showing 5 articles and there are approx 3,000 articles currently.

There is no way to create a sort like this in Views. I can separately create the two views above no problem, but I can't seem to figure out how to merge them into one list. I have tried using a View Attachment to attach a view of the checked and future articles above the other articles but the attachment appears on all pages of the pager. And if there are more that 5 articles in the attachment, then they are the only ones you ever see when paging.

So from what I can see I need to use a views_query_alter hook or maybe a views_data_alter hook to programmatically alter the sort of query but coding it not my forte and I'm a bit lost. I've created mymodule.info.yml and mymodule.views_execution.inc and placed the two of them in a folder in modules->custom. My info file is

type: module
name: 'Views Article Custom Sort'
description: 'Custom Sorting of Articles'
package: 'custom'
core_version_requirement: ^9 || ^10

dependencies:
  - views

My mymodule.views_execution.inc

<?php

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

function article_custom_sort_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'article_list' && $view->current_display == 'block_1') {
    $query->addField(
    NULL,
    'CASE WHEN (COALESCE(field_display_in_sticky_until, 0) >= CURRENT_TIMESTAMP AND COALESCE(field_stick_to_top_of_list, 0) == TRUE)',
    'sticky'
    );
    $query->addOrderBy(
    NULL,
    NULL,
    'ASC',
    'sticky'
    );
  }
}

Firstly, the module is not showing on my Module page, so I can't enable it and I'm fairly sure my code is full of errors. If anyone can help me, I'd really appreciate it.

Thanks

F

P.S. I had thought that maybe an alternative is to have the boolean unchecked if the date field is in the past when cron is run, but I have no idea where to start with coding that one. If I could get that working, then I could simply sort the view by the boolean field followed by published date. Sadly the module Scheduler only publishes and Unpublishes nodes on cron, it doesn't offer options to alter other fields.

Answer 1

In case anyone comes across this question, I used a different route to solve my problem. Rather than trying to merge two Views into one list, I use the module ECA: Event - Condition - Action to unselect the Boolean Field on Cron, so now I can sort my View by the Boolean Field followed by sorting by Published Date.

ECA is an excellent module by the way. It allows me to execute a View and then perform an action on the Views Results. It's similar to Rules, except it actually works with D9/D10. So I created a View that lists Nodes of type Article that have the Boolean Field selected and have a date in the past in the Custom Date Field. On Cron, the View is executed, and the Boolean Field is unselected and saved in each Node in the Views Result. So after cron, the only Articles that have the Boolean Field selected are the ones with the Custom Date in the future.