Sort View by two date fields

The issue is more relevant to Views module than the Date module that it was posted to.

Updated with lots of research on possible solutions & work-arounds.

Also, marked as won't fix because it is possible to solve with views hooks. Though, a contrib module to provide an interface for this would be delightful.

fixed typo and issue reference

I'm a little confused about exactly where the two different fields are supposed to be substituted in the code (please forgive my ignorance of PHP). It looks as if the code only accommodates for one field: field_date_publication—I don't understand where the other date field is. If I have two different date fields, say, field_schedule_consistentdates and field_schedule_customdates, how would they be plugged into this equation?

EDIT: Sorry... I just realized the other field was the node creation date.

Thank you so much for the AWESOME writeup. Just wanted to include an example of sorting by more than two date fields for anyone else who may be struggling like I was.

I ran into some issues and had to lookup proper syntax for the CASE function. The extra WHEN / THEN statements got me what I needed. Thanks again for the great analysis. I was on the verge of adding an additional universal date-sort field before I stumbled across this.

The example below sorts by ascending dates for the following fields: date_time, date_time_range, date

function MODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'views_name' && $view->current_display == 'display_name') {
    $query->orderby = array(
	  array(
        'field' => 'CASE WHEN field_data_field_date_time.field_date_time_value THEN field_data_field_date_time.field_date_time_value WHEN field_data_field_date_time_range.field_date_time_range_value THEN field_data_field_date_time_range.field_date_time_range_value ELSE field_data_field_date.field_date_value END',
        'direction' => 'ASC',
      )
    );
  }
}

If I want to sort by the first value of a date field with start and end values?
I tried this code but won't work:

/**
 * Implements hook_views_query_alter
 * @param type $view
 * @param type $query
 */
function custom_ord_bandi_views_query_alter(&$view, &$query) {
	if ($view->name == 'home_bandi' && $view->current_display == 'page') {
		$query->orderby = array(
			array(
				'field' => 'CASE WHEN
field_data_field_name1.field_name1_value THEN 
field_data_field_name1.field_name1_value ELSE 
field_data_field_name2.field_name2_value1 END',
				'direction' => 'DESC',
			)
		);
	}
}

(in this case name2 field has two values, start and end)

#7 It worked perfectly for me. Great thanx … i was about to spend days searching …:)

For anyone who has a situation where one of your date fields is in a Field Collection, this worked for me in D7 with Views 3.

In MODULE.module:

/**
 * Implements hook_views_api().
 */
function MODULE_views_api() { // your module name into hook_views_api
  return array(
    'api' => 3, // Views 3
    // might not need the line below if your files are in the module folder root, but in any case, the last arg is the name of your module
    'path' => drupal_get_path('module', 'module_name'),
  );
}

In MODULE_views.inc:

/**
 * Implements hook_views_query_alter().
 */
function MODULE_views_query_alter(&$view, &$query) {
  
  if ($view->name == 'view_name' && $view->current_display == 'display_name') {
    
    // to find out what the fieldname is: use Devel and add your desired field as 
    // first filter ( =>orderby[0]). install Devel and uncomment the next line
    // dpm($view->query->orderby);
    
    // Completely replace current orderby
    $query->orderby = array(
      array(
        // you need to actually do WHEN ... THEN once for each field, then a fallback
        'field' => 'CASE WHEN field_data_field_date_field_date_value THEN field_data_field_date_field_date_value WHEN field_collection_item_field_data_field_safety_date_location__1 THEN field_collection_item_field_data_field_safety_date_location__1 ELSE node.created END',
        'direction' => 'ASC',
      )
    );
  }
}

Hi,
Hoping someone can help shed some light on this. I have a "public title" field on nodes that site editors can define. My hope is that if there is a value in this field, it will use that as the sort, otherwise I want it to use the node title. Here's my code in query alter. Any help is greatly appreciated!

$query->orderby = array(
	array(
		'field' => 'CASE WHEN field_data_field_public_title.field_public_title_value THEN field_data_field_public_title.field_public_title_value ELSE node.title END',
		'direction' => 'ASC',
		)
	);

Re this solution:

/**
 * Implements hook_views_query_alter
 * @param type $view
 * @param type $query
 */
function MODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'views_name' && $view->current_display == 'display_name') {
    $query->orderby = array(
      array(
        'field' => 'CASE WHEN field_data_field_date_publication.field_date_publication_value THEN field_data_field_date_publication.field_date_publication_value ELSE node.created END',
        'direction' => 'DESC',
      )
    );
  }
}

I'm getting errors in the specified view saying that it cannot find the column even when I'm using the table and column name direct from the database.

So in the example about the error I'm getting it:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_date_publication.field_date_publication_value' in 'order clause'

#7 works great! Thanks so much.

Hi,
I used this sandbox to combine two different date fields in my Views sort, works great:
https://www.drupal.org/sandbox/hazah/2285127

@maxplus thanks for the link, but I tried the sandbox and it didn't work for me

I am not very active in this community, but I faced the same issue and maybe my solution helps someone. It is based on the Search API module and the Search API Combined Fields module and works like charm (for my purposes).

1. Create an index including the content types you wish to order by date
2. Add the date fields to the Index
3. Create a combined search api field, which combines these different field names to one new field
4. Choose the new search index in the setup screen of your new view
5. Create a sort desc/asc by the new combined field

... without any written lines of code.

@sensedrive. Was this approach for Drupal 7 or 8?

I guess it's a similar approach for either, but interested in your experience.

My problem solved by following 'case when' for orderby in views query alter...I wanted to sort views list by custom table weight and node changed date.

Thanks

Re #12. I had a similar problem with unknown columns being flagged. If you print $query you will see arrays specifying the tables and fields that the Views module will use. I guess if would be possible to add the additional ones needed via the hook implementation code and so eliminate the error. However, I found the simplest way to achieve this was to include all the fields I intended to use in my hook implementation in the "Sort Criteria" on the Views edit form. The corresponding tables and fields will then appear in the $query data structure.

My problem solved by following below code. Below code is used for two content type date fields for sorting using views.

function modulename_views_query_alter(&$view, &$query) {

if ($view->name == 'views_name' && $view->current_display == 'display_name') {

// Completely replace current orderby
$query->orderby = array(
array(
// you need to actually do WHEN ... THEN once for each field, then a fallback
// Here added 2 content type date fileds
//two fields are for two content types i.e field_data_field_smk_date and field_data_field_pub_date
'field' => 'CASE WHEN
field_data_field_smk_date.field_smk_date_value THEN field_data_field_smk_date.field_smk_date_value
WHEN
field_data_field_pub_date.field_pub_date_value THEN field_data_field_pub_date.field_pub_date_value
ELSE node.created END',
'direction' => 'DESC',
)
);
}
}

For Drupal 9.0.6 I had to do this way (in my case I have to sort two types of MEDIA entity on the same view, one that use a "field_date" and another one a "field_start_date", you can replace MEDIA by NODE or whatever entity type you use)

function MYMODULE_views_query_alter( $view, $query )
{
  switch ( $view -> id() )
  {    
    case 'my_view':
      $query -> orderby = [];
      $query -> addOrderBy( NULL, 'CASE WHEN media__field_date.field_date_value THEN media__field_date.field_date_value WHEN media__field_start_date.field_start_date_value THEN media__field_start_date.field_start_date_value ELSE media_field_data.created END', 'DESC', 'order_field' );      
    break;
  }
}

A solution that works for me

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

function MODULE_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'your_view_id' && $view->current_display == 'your_display_name') {
    $query->addField(
    NULL,
    'GREATEST(COALESCE(field_date1_value, 0), COALESCE(field_date2_value, 0))',
    'new_date'
    );
    $query->addOrderBy(
    NULL,
    NULL,
    'ASC',
    'new_date'
    );
  }
}

Thank you @pgshehata.

Your solution works for on Drupal 9.1.X, I added a custom field(called User Name) on my View because I don't have a user fullname. It combines {{ first_name }} &
{{ last_name }}. Now I need to add direction to make it sortable either by ASC or DESC.

Thank you guys for this Question and solutions, I've been searching and searching.

@Sensedrive - #16 worked great for me. Thanks!