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.
Comment | File | Size | Author |
---|---|---|---|
#24 | date_sorting.png | 95.1 KB | eigilb |
Comments
Comment #1
ericm CreditAttribution: ericm commentedI 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.
Comment #2
mstef CreditAttribution: mstef commentedYeahhhh 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..
Comment #3
jgoodwill01 CreditAttribution: jgoodwill01 commented+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.
Comment #4
Mores CreditAttribution: Mores commentedHi 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.
Comment #5
jgoodwill01 CreditAttribution: jgoodwill01 commentedI guess we have no progress on this?
Comment #6
jgoodwill01 CreditAttribution: jgoodwill01 commentedSwitching to bug report..... This really is a problem for functionality when two date fields are being sorted.
Comment #7
Exploratus CreditAttribution: Exploratus commentedsubscribe. I need this as well..
Comment #8
twistedindustries CreditAttribution: twistedindustries commentedsubscribe 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.
Comment #9
Slovak CreditAttribution: Slovak commentedNo need for grouping. Use one Date field with multiple settings. I had documented my solution: Sort Multiple Formats of Date Fields in Views
Comment #10
jgoodwill01 CreditAttribution: jgoodwill01 commentedSlovak,
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.
Comment #11
KarenS CreditAttribution: KarenS commentedThis 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.
Comment #12
longwaveYes, 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.
Comment #13
jgoodwill01 CreditAttribution: jgoodwill01 commented+1 I would love to see this feature. I have been frustrated with sorting two dates for several years now.
Comment #14
RobW CreditAttribution: RobW commentedThere 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:
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.
Comment #15
twistedindustries CreditAttribution: twistedindustries commentedThe 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?
Comment #16
RobW CreditAttribution: RobW commentedThere'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.
Comment #17
KingSalibah CreditAttribution: KingSalibah commentedI 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.
Comment #18
Camario39 CreditAttribution: Camario39 commentedYup 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) :)
Comment #19
Encarte CreditAttribution: Encarte commentedI 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?
Comment #20
oscarjgarza CreditAttribution: oscarjgarza commented+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. .
Comment #21
KarenS CreditAttribution: KarenS commentedI 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.
Comment #22
eigilb CreditAttribution: eigilb commentedThis 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):
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.
Comment #23
KarenS CreditAttribution: KarenS commentedNothing 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.
Comment #24
eigilb CreditAttribution: eigilb commentedAssuming 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.
Comment #25
eigilb CreditAttribution: eigilb commentedComment #26
KingSalibah CreditAttribution: KingSalibah commented[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?
Comment #27
imclean CreditAttribution: imclean commentedAn 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:
The CASE clause can be modified to suit your requirements, this only checks for the existence of the fields.
Comment #28
imclean CreditAttribution: imclean commentedIf both fields are always present you could sort by whatever field is next using something like this:
Comment #29
eloone CreditAttribution: eloone commentedIn drupal 7 views 3 the syntax becomes:
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 :
This will override only the first sorting condition.
Comment #30
Matthew Davidson CreditAttribution: Matthew Davidson commentedHere'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.
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.
Comment #31
Renee S CreditAttribution: Renee S commentedThanks, eloone, exactly what I needed. Since this isn't stackoverflow I can't upvote you, so this will have to do ;)
Comment #32
dooug CreditAttribution: dooug commentedThis 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().
Comment #34
hazah CreditAttribution: hazah commentedAs @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!
Comment #35
dealancer CreditAttribution: dealancer commentedThat's a very good sandbox module. It would be nice to include it into the Date module!
Comment #36
jon.skn3 CreditAttribution: jon.skn3 commentedA 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.
Comment #37
echoz CreditAttribution: echoz commented@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!
Comment #38
rakesh.nimje84@gmail.com CreditAttribution: rakesh.nimje84@gmail.com commented#34 worked for me. Nice effort.
Comment #39
dmkelner CreditAttribution: dmkelner at Sleight-of-Hand Studios LLC commentedI'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.
Comment #40
imclean CreditAttribution: imclean commented@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.