I can't believe this problem hasn't been reported, but I can't find any mention or solution. Please just point me to it, if it exists. I am trying to combine CCK fields in a sort, sorted by date.

My problem is that I have two event types, a one-time event and a repeating event, each with its own datetime field (field_datetime and field_datetime_repeating). I wanted to create a combined View listing sorted by date, but there doesn't seem to be a way to combine the sort to use both fields. The reason why I have two different fields is that the non-repeating datetime widget allows you to specify a non-consecutive set of dates, so that an one-time event can be on (for instance) January 1, 3, and 5. This is useful. The repeating datetime widget, on the other hand, allows you to specify automatically generated repeats, but not non-consecutive dates per event iteration. That makes sense, since implementing a repeat held every year on Jan 1, 3, 5 sounds like a coding nightmare. So to make all options available to event creators, I made two node types.

But now I want to make a block that simply lists all upcoming events of both types, sorted by date. The problem is that if I add both fields to the Views sort, they are grouped by type and then sorted within each group. The calendar module happily takes events of both types because it doesn't sort, it just places events into their day bin on the grid. I'm stumped.

So can I have it all, a single block for both event types sorted by date? Or do I have to make two blocks, one for each type? Any ideas would be gratefully appreciated.

CommentFileSizeAuthor
#24 date_sorting.png95.1 KBeigilb
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

ericm’s picture

Component: Miscellaneous » Date CCK Field

I see that there used to be a "Switch Calendar block" for Calendar 1.x and earlier that allowed "the user to switch between calendar, LIST, table, teaser, and full node views for whichever time period is being viewed." Apparently that was removed in the change to Calendar 2.x, so perhaps Calendar no longer has the ability to generate a list view. Bummer.

mstef’s picture

Yeahhhh I really need this too...

There seems to be a combination option for the filters...there should definitely be one for sorting..this has to be something required by many..

jgoodwill01’s picture

+1 on this. I really need to find a way to sort by two different date fields since I have two date field one repeating and one multiple date for recurring and irregular recurring events.

Much appreciation to anyone that can figure this out.

Mores’s picture

Hi all,

Got the same problem (1 single date field + 1 double date field (from - to) to sort).

Does someone knows where to change the sort function in views? Or do I have to do this in the date module?

I think it would be nice if I could add sorting with "ORDER BY COALESCE". Then we should be able to sort on 2 fields together.

jgoodwill01’s picture

Version: 6.x-2.0-rc6 » 6.x-2.4
Priority: Normal » Critical

I guess we have no progress on this?

jgoodwill01’s picture

Category: support » bug

Switching to bug report..... This really is a problem for functionality when two date fields are being sorted.

Exploratus’s picture

subscribe. I need this as well..

twistedindustries’s picture

subscribe I also need this for grouping fields on a table view. I think the same functionality in arguments and filters where you can choose an AND/OR for multiple date fields is all we need.

Slovak’s picture

No need for grouping. Use one Date field with multiple settings. I had documented my solution: Sort Multiple Formats of Date Fields in Views

jgoodwill01’s picture

Slovak,

I'm not sure you solution would correct the issue we experience with our date system. We have to have two date types one for regularly repeating dates and one for line by line regular repeats. Granularity isn't really the issue at hand.

KarenS’s picture

Category: bug » feature
Priority: Critical » Normal

This sounds like it might be a feature request but I can't tell. I can't figure out what people think is broken or if it still is or what they want it to do.

longwave’s picture

Yes, this is a feature request - it is necessary if you cannot share date fields between content types in the cases noted above. Some way of specifying "ORDER BY COALESCE (datefield1, datefield2)" in Views would be the easiest way to solve it I think, as noted in #4, so perhaps this should be a Views feature request instead - http://drupal.org/project/views_or seems to do this for filters but not sort criteria unfortunately.

jgoodwill01’s picture

+1 I would love to see this feature. I have been frustrated with sorting two dates for several years now.

RobW’s picture

Version: 6.x-2.4 » 7.x-2.x-dev
Category: feature » support
Status: Active » Closed (fixed)

There is a relatively easy way to get this working using the computed fields module. Computed fields creates a field that is populated by php, with no user input on the node form. So with a little code you can give your computed field the value of all the separate fields you want to sort, and then create a view that sorts your computed field.

Here's an example coded for D7. If I had two content types, one with field_date_1 and the other with field_date_2:

  1. Create a computed field
  2. Provide it with code like
    //Check if this node has date field 1.
    if (!empty($entity->field_date_1[LANGUAGE_NONE][0]['value'])) {
    
      //Assign the computed field the value of the date field. Convert the date field value into a unix timestamp for simple numeric sorting.
      $entity_field[0]['value'] = strtotime($entity->field_date_1[LANGUAGE_NONE][0]['value']);
    
    } 
    //Rinse and repeat.
    elseif (!empty($entity->field_date_2[LANGUAGE_NONE][0]['value'])) {
    
      $entity_field[0]['value'] = strtotime($entity->field_date_2[LANGUAGE_NONE][0]['value']);
    
    }
    //And if there's no date added at all:
    else {
    
       $entity_field[0]['value'] = NULL;
    
    }
    
  3. Make sure "Store using the database settings below (required for Views use)" is checked. Select Data Type = integer because we've converted to a timestamp.
  4. Attach this computed field to both your content types, and configure its display in the manage display tab. Since this field is a restatement of already existing fields you're probably going to want to set it to hidden.
  5. Create a view that sorts based on the computed field that has been populated by the values of your two separate date fields. Voilà!

You can get really crazy because $entity in the computed fields code is the $node object. Switch based on node type, other field values, what is empty and what is not, etc.: anything you can do with php. I used it on my last project to order a news feed by node created date OR by a date field if it was filled in and occurred after the node created date.

One caveat: if you're adding this solution after your site has content, be aware that the computed field is only populated when the node is saved, so you'll have to go through and resave all of your existing nodes.

If people wanted this functionality out of the box it would really be provided by views, not date. Merlin has stated that this is a 'won't fix' in #499158: Combine Sort Criteria Instead of Weight?, and since computed fields offers a solution, I'm going to mark this issue closed and fixed. I changed the issue version to 7x because my example code is written for the 7x computed fields module, but the concept is equally applicable to D6.

twistedindustries’s picture

The above may work for some use cases but is there a way to sort if say you have two node types (node_type_1) and (node_type_2) with three date fields date_1(node_type_1) , date_2(node_type_1) , and date_3(node_type_2). Is there a way possibly using hook_views_query_alter to take all those fields and sort them without separating them?

RobW’s picture

There's probably a better way to do it, but I believe you could use code similar to my above example, adding a second else if for date_3, then attach the computed field to both content types, then sort by that single field in a view. A possibility until you find something more elegant.

KingSalibah’s picture

I wish the "date" type in Drupal was a core element. I myself have so many different types of data that all revolve around dates and it seems to me that if date became more centralized and part of the system, more things could easily be created, such as timelines, queries between such and such date easier and faster.

One of the problems that I currently have is I have field_event_date and I have _field_articledate that I want merged into one column whereby I can sort as one entire column. This doesn't appear to be an out-of-the-box simple function to do.

I must say though that I just downloaded the latest date and calendar modules and as part of this a view called "Date Browser" was created. This appears to merge both date fields into one. However, it doesn't handle my display needs. I would prefer since both field types are dates that I could just combine them, in the same way that "title" gets combined.

Camario39’s picture

Yup I need this too. This is where I miss the ability to write the query directly. It would be two queries with a simple UNION and then sorted.

Select title, date from ( Select title, dateDue from cckA) union (select title, dateTo From cckB) order by date desc

In the mean time i'll have two seperate views for each of my content types.

I wish i knew how to contribute to these modules. (and had time) :)

Encarte’s picture

Component: Date CCK Field » Code
Category: support » feature
Status: Closed (fixed) » Active

I think this is a legitimate feature request for Date module. Even if you could use Computer field module and code for some use cases, that doesn't solve all use cases and doesn't seem like a user or performance friendly solution. There may not be a crowd interested in this, but there are some people interested and, so, I think it's still a good feature request.

Impossible?

oscarjgarza’s picture

+1 for Views feature request - this is a serious issue for wanting to have separate content types with separate date fields, then sorting them in a combined manner. Don't see how this is a feature request for Date, though. .

KarenS’s picture

Status: Active » Postponed (maintainer needs more info)

I am not sure you could do this even if you wrote a custom query. The only suggestion I can see for how anyone thinks it is possible to sort by multiple fields is the example in #12, and we can't do that because COALESCE is not cross database compatible. And I'm not 100% convinced it would work right anyway.

The starting point is to 1) figure out what changes to the query would work at all, and then you have to 2) change that into something that will work in all the databases we support, and then we have to 3) back up and figure out how to make views produce that query. I'm not convinced even #1 is possible, and without that there is nothing else that can be done.

eigilb’s picture

This is also similar to what is reported as a calendar issue in http://drupal.org/node/1441322. It is also an example screenshot at http://drupal.org/files/d7_calendar.png showing this.

As I suggested in that post (referring to the repeating dates form):

By including a time field in addition to the date field for "Include dates" option it would be possible to avoid the non repeating option all together. Both reqirements would be fulfilled by using one single date field type. I also suspect that the generated SQL would be simpler and faster.

And this should include both start and end times.

I will add that there is also a feature request for a time field in addition to the date field in: http://drupal.org/node/1053158
and a patch to add a start time field, but not an end time field.

KarenS’s picture

Nothing in #22 tells me how you would construct a sql query that would sort correctly on two dates. Telling me over that you want this is not going to take this any where. What sql could possibly create the right result? Adding a time field to the exception is not going to make any difference to this question or to the sql.

eigilb’s picture

FileSize
95.1 KB

Assuming that I am talking about two different date types, I can understand that this isn't going anywhere. But in #22 I ask for addition of both start and end times to the "Include dates" that allready is at the end of the repeating dates form.

That should make it possible to avoid the use more than a single date field to achieve the functionality and results that I and several others have asked for.

As a prof of concept i have added a series of repeating dates (weekly on fridays). In addition I have added some additional dates with the option "Include dates". Afterwards I have manually modified some of the entries directly in the database (both dates and times), disregarding the repeat rules.

As can be seen on the attached picture, all dates are perfectly sorted.

eigilb’s picture

Status: Postponed (maintainer needs more info) » Active
KingSalibah’s picture

[Without thinking about how this works structurally for a moment...] Since time is an every continuing thing and no matter what anyone says a date is a date, and while you can assign or change a date, all information revolves around time and there isn't really a difference amongst date types between content types. For example, if you have a content type for "articles" with a date field and then a content type for "photos" with a date field, logically you should be able to ask "What happened on March 22, 2012?" regardless of article or photo and get either, or have a choice. Thus, why couldn't there be one global date function that all data aligns to and which Views could request from?

Similarly, the Title field in Drupal appears to function the way you would want the date feature to function, i.e., whether you choose "articles" or "photos" the title will be listed in a View in one list and can be sorted as such, thus the function to merge the Title field (in concept) should be able to be done with the date field. No?

imclean’s picture

An alternative to #14 and along the lines of #15, I've successfully used hook_views_query_alter() to change the ORDER BY to sort by 2 different date fields using Views 6.x-3.0.

Example: 2 content types.

- Show - field_dates: multiple value date field
- Event - field_event_dates: date field with repeats

The 2 content types need to be sorted correctly in the same view by different date fields.

1. Create the view with both the date fields (and appropriate filters)
2. Add both date fields to the sort criteria
3. Preview the view, noting the field names in ORDER BY in the SQL.
4. Override the ORDER BY using hook_views_query_alter()

In our case:

/**
 * Implementation of hook_views_query_alter().
 */
function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'view_name') {   
    // Completely replace current orderby
    $query->orderby = array(" CASE 
        WHEN node_data_field_dates_field_dates_value THEN node_data_field_dates_field_dates_value
        WHEN node_data_field_event_dates_field_event_dates_value THEN node_data_field_event_dates_field_event_dates_value 
    END ASC ");
  }
}

The CASE clause can be modified to suit your requirements, this only checks for the existence of the fields.

imclean’s picture

If both fields are always present you could sort by whatever field is next using something like this:

 $query->orderby = array(" CASE 
        WHEN 
            node_data_field_dates_field_dates_value <= node_data_field_event_dates_field_event_dates_value 
        THEN 
            node_data_field_dates_field_dates_value
        WHEN 
            node_data_field_event_dates_field_event_dates_value < node_data_field_dates_field_dates_value 
        THEN 
            node_data_field_event_dates_field_event_dates_value
        END ASC ");

eloone’s picture

In drupal 7 views 3 the syntax becomes:

/**
 * 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',
      )
    );
  }
}

Fill with your own fields. This will override the entire orderby condition in your query, if you need to just add a condition to your orderby use a syntax that more looks like :

$query->orderby[0] =  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',
      );

This will override only the first sorting condition.

Matthew Davidson’s picture

Here's a more convoluted alternative to the workaround #14. Similar approach, but taking computed field out of the mix and kludging two fields to work as a single field that can contain either repeating values or unlimited arbitrary values. We have an 'event' content type with a 'field_event_date' field for storing the dates we actually work with, and a 'field_event_recurring_date' field for setting the repeat rule and calculating the dates.

We hide the date input elements in 'field_event_recurring_date' in the form widget, and copy the first value from 'field_event_date' to there before the repeats are calculated, and afterward we copy the resulting values to 'field_event_date'. You have to be careful about using the same field (and maybe widget) types for both fields, and the code below may have to be tweaked if you're using additional date modules.

/**
 * Implements hook_form_FORM_ID_alter().
 */
function MYMODULE_form_event_node_form_alter(&$form, &$form_state, $form_id) {
  array_unshift($form['field_event_recurring_date'][$form['language']['#value']][0]['#element_validate'], 'MYMODULE_date_combo_validate');
}

/**
 * Implements hook_element_info_alter().
 */
function MYMODULE_element_info_alter(&$type) {
  $type['date_combo']['#process'][] = 'MYMODULE_date_combo_process';
}

function MYMODULE_date_combo_process($element, &$form_state, $form) {
  if ($element['#field_name'] == 'field_event_recurring_date') {
    $element['show_todate']['#access'] = FALSE;
    $element['value']['#access'] = FALSE;
    $element['value2']['#access'] = FALSE;
    $element['show_repeat_settings']['#description'] = t('Repeating event times are calculated using a repeat rule and the first event date value above. If you have multiple values above and specify a repeat rule, all but the first will be overwritten by the calculated values.');
  }
  return $element;
}

function MYMODULE_date_combo_validate($element, &$form_state) {
  $lang = $form_state['values']['language'];
  if (!empty($form_state['input']['field_event_recurring_date'][$lang][0]['show_repeat_settings'])) {
    $form_state['input']['field_event_recurring_date'][$lang][0]['value'] = $form_state['input']['field_event_date'][$lang][0]['value'];
    $form_state['input']['field_event_recurring_date'][$lang][0]['value2'] = empty($form_state['input']['field_event_date'][$lang][0]['value2']['date']) ? $form_state['input']['field_event_date'][$lang][0]['value'] : $form_state['input']['field_event_date'][$lang][0]['value2'];
    $form_state['input']['field_event_recurring_date'][$lang][0]['show_todate'] = $form_state['input']['field_event_date'][$lang][0]['show_todate'];
    if (module_exists('date_all_day')) {
      $form_state['input']['field_event_recurring_date'][$lang][0]['all_day'] = $form_state['input']['field_event_date'][$lang][0]['all_day'];
    }
  }
  else {
    $form_state['input']['field_event_recurring_date'][$lang][0]['value'] = '';
    $form_state['input']['field_event_recurring_date'][$lang][0]['value2'] = '';
    $form_state['input']['field_event_recurring_date'][$lang][0]['show_todate'] = '0';
  }
}

/**
 * Implements hook_node_presave().
 */
function MYMODULE_node_presave($node) {
  if ($node->type == 'event' && !empty($node->field_event_recurring_date[$node->language])) {
    foreach ($node->field_event_recurring_date[$node->language] as $delta => $item) {
      unset($item['rrule']);
      $node->field_event_date[$node->language][$delta] = $item;
    }
  }
}

Just to reiterate: this is Wrong, Bad, not a long term solution, may cause drowsiness, and has been statistically correlated with a high incidence of cancer in rodent test subjects, but may suffice as a short term fix in some cases.

Renee S’s picture

Thanks, eloone, exactly what I needed. Since this isn't stackoverflow I can't upvote you, so this will have to do ;)

dooug’s picture

Category: Feature request » Support request
Issue summary: View changes
Status: Active » Fixed

This was duplicated by #2133879: Sort View by two date fields. I also summarized many of these work arounds on that issue.

This feature request should probably be re-assigned to Views. However, since using hook_views_query_alter() seems to provide an adequate solution, I'm just marking it as fixed. If people need a UI to make this happen, then let's create a contrib module for that!

Special thanks to @imclean and @eloone for providing examples of hook_views_query_alter().

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

hazah’s picture

Status: Closed (fixed) » Active

As @dooug suggested, I've created one such module. You can find it here: https://drupal.org/sandbox/hazah/2285127

It lets you use the coalesce to combine function and the UX is modelled after the filter handler, so you can select the same fields. Let me know if this works for you!

dealancer’s picture

That's a very good sandbox module. It would be nice to include it into the Date module!

jon.skn3’s picture

A solution for anyone reading this:

Add a new date field called "Sort Date" to each of the content types you want to share sorting. The date field should include enough detail in order to cover all of your separate date fields. So for example if one of your content types captures seconds, then the Sort Date field should also capture seconds.

Using the rules module add a new rule for each content type that you want to share sorting. The rule is responsible for populating the Sort Date field when the node is saved. Each rule can retrieve the correct content type date and pump it into the Sort Date. So for example: before saving content type "news" set data value of sort_date to value of news_date.

Once you have this you are then free to sort by this field in views.

If you need to update existing content and apply the rules, simply select them in the content list and apply one of the bulk actions to trigger the node to be re-saved.

The final step is optional, you can prevent your editors/administrators from seeing this Sort Field by using field permissions module. Remember that account 1 (administrator) will always be able to see the field regardless.

echoz’s picture

@eloone your code in #29 was a big solution for me, thank you!

Does anyone know how I can make my second field sort by it's delta 0, in other words, the first of a date repeat field, rather than the next chronological date of all the repeats? Much appreciated!

rakesh.nimje84@gmail.com’s picture

#34 worked for me. Nice effort.

dmkelner’s picture

I've hit this issue as well, and the solutions, while interesting, only handle the use case for sorting. I inherited four content types with four different date fields. They are of mixed types. I need a single field to express the date so that I can not only sort, but also check for how old the content is. I also want to take the date and reformat it to just get the year and use the year as a grouping value. I can do all of this with a real date field, but I can't figure out how to get four disparate dates into a single computed date field.

imclean’s picture

@dmkelner, you'd probably need to do it in SQL. Using the above examples, which support multiple different date fields, you can expand them to format the dates via SQL and compare/group as you see fit.