0

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.

2
  • why is the hook in a file named mymodule.views_execution.inc? it should probably be in a file named mymodule.module. In hook functions, the HOOK part of the function name needs to be the same as the module filename. eg. your article_custom_sort_views_query_alter should be mymodule_views_query_alter
    – 2pha
    Jun 3, 2023 at 8:26
  • Thanks @2pha. I had previously tried using the .module file type but it didn't work, which is why I tried the .inc file type (I had read somewhere that that was what I needed). Anyway, I've since discovered I had a typo in my file name which once I fixed and also changed filetype to .module, I can now enable the module. However, as I thought, there are errors in my code. I'm getting 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax'. Do you know how I can access field values from each row of the view in my views_query_alter? Jun 13, 2023 at 14:43

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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