After numerous attempts over the last 9 months, I've finallly gotten getResources selection, sorting and filtering according to a date template variable to work. Thought I would save other MODX users time & trouble by writing about it.
First, why sort on date? Well, because presentations such as calendars and lists of events are naturally date-ordered. I almost gave up on sorting by date TV, since the built-in unpub_date resource field offers semantically ALMOST the same thing: It's usually fine to unpublish a notice once an event is underway. Sorting by a resource field in getResources is straightforward. Sorting by TV involves fiddling with several substitute or additional getResources properties and options. Failure to provide getResources with exactly what it needs / wants results in puzzling output, without error messages or other hints.
So, if you can get by without sorting on a date TV, I recommend you do that.
However, "semantically almost the same" isn't always enough. The date upon which some event is to commence is not quite the same as the date upon which to stop publicizing it. How about events that run across multiple days, for example?
Besides, I had already entered TV dates for over 1000 events logged into a MODX Evolution site over the previous two years. I was in the midst of porting this site over to Revolution, replacing ditto with getResources. This was a technology refresh, so I didn't want to go backward. And most certainly I didn't relish the prospect of cutting & pasting dates from the TV tab into the Settings tab for 1000+ resources!
So I updated getResources to the latest version (1.5.0-pl) on my development version of Revolution (2.2.0-pl2) and carefully followed the document at
http://rtfm.modx.com/display/ADDON/getResources. I temporarily deleted the 'tpl' property reference in order to view the getResources dump.
I encountered bizarre results. After many experiments, I learned that a defective getResources call returns the first N resources ascending-ordered by ID number, where N is specified by the getResources 'limit' property. I also noted that a seemingly correct getResources call sorting on my date TV would return an unexpected set of resources, and these not even presented in correct date order! However, the call delivered the same results repeatably, and cache-clearing on host and client made no difference.
What to do?
About to lose hope, I had a breakthrough. By instrumenting the template to which my event date TV was attached, I noticed that the TV date instances were not of uniform format. It was a mixed bag:
- YYYY-mm-dd HH:MM:SS (24-hour time format)
Example: 2010-06-04 15:30:23
- Month-spelled-out day-with-leading-zero-suppressed, YYYY h:m AM||PM
Example: June 4, 2010 3:30 PM
- M-with-leading-zero-suppressed/D-with-leading-zero-suppressed/YYYY h:m AM||PM
Example: 6/4/2010 3:30 PM
- M-with-leading-zero-suppressed/D-with-leading-zero-suppressed/YY h:m AM||PM
Example: 6/4/10 3:30 PM
I confirmed this by checking the database using phpMyAdmin.
What a mess! I don't know where all of those format variations came from. I must have caused them by monkeying around with the date TV's Input and Output Options.
Apparently, ditto on Evo had no problem with the format mish mash, but GetResources on Revo doesn't like it. getResources apparently REQUIRES the first of the above four formats, which I suppose is what "&sortbyTVType= `datetime`" is all about. Of course! It's unreasonable to expect sorting to work on arbitrary date formats, and that first format is so easy and efficient to sort.
Meanwhile, the built-in resource fields, such as the 'createdon' date, are also formatted in the first way given above.
A big clue came in jrotering's forum post,
http://forums.modx.com/?action=thread&thread=74320. His sole error was getting the capitalization of a getResources property wrong. Once he fixed that, he (rather amusingly) revealed that his call worked properly! Note that he carefully explained how his date TV instances were formatted.
This encouraged me to regularize my date TV instances. It wasn't so bad a chore. Database table 'modx_site_tmplvar_contentvalues' contains all of the date TV instances. I used phpMyAdmin to examine and edit the incorrectly formatted ones.
The date instances having been fixed, I'm happy to report that the following getResources call selects and sorts as desired on date TV:
[[!getResources?
&parents= `6`
&depth= `1`
&showUnpublished= `1`
&showDeleted= `0`
&includeContent= `1`
&tvPrefix= ``
&includeTVs= `1`
&processTVs= `1`
&processTVList= `eventDate`
&sortbyTVType= `datetime`
&sortbyTV= `eventDate`
&sortdirTV= `DESC`
&limit= `5`
&tpl= `homeMainEventsTpl`
]]
- Remove the 'tpl' property in order to enable the properties & options dump.
- Ensure that you're using the back-tilted single quotes in the snippet call. I Don't mean to insult anyone's intelligence. Failing to do that is a frequent error of mine.
- Ensure that spellings and capitalizations of property names are correct.
- Ensure the same for template variables.
- Using phpMyAdmin, check TV dates in the database and ensure all are of the form discussed above.
- To avoid future format problems, ensure that the Input Options for the date TV upon which you intend to sort is specified as
'Input Type: Date'
'Allow Blank: No'
and the Output Options as
'Output Type: Date'
'Date Format: %Y-%m-%d %H:%M:%S'
Next problem: Restricting resources to dates in the past or in the future. There is no point showing past events to users hoping to plan ahead. Likewise, there is no point in showing future events to users whose interest is historical.
A gentleman writing under the agreeably musical name debussy suggests (
http://forums.modx.com/thread/43727/getresources-to-show-current-and-future-events#dis-post-419669) using a custom snippet, which I have named "currentDatetime."
<?php
return date('Y-m-d H:i:s');
?>
Use this snippet as part of yet another getResources property, "&tvFilters." This version of the property restricts getResources to reporting on the future:
&tvFilters= `eventDate>>[[!currentDatetime]]`
and this version restricts getResources to reporting on the past:
&tvFilters= `eventDate<<[[!currentDatetime]]`
=====================
I hope that the MODX folks add date-TV selection, sorting and filtering to
http://rtfm.modx.com/display/ADDON/getResources.Examples, and that Bob Ray covers it in the next edition of his excellent book.
[ed. note: halfnium last edited this post 10 years, 9 months ago.]
MODX Staff
