The decision to store all tv values as text is, frankly, awful. I’ve spent two years dealing with aspects of this bad design decision. The basic incompatibilities between how PHP and Unix think about dates, and how MySQL stores them did not need another layer of confusion where elaborate conversions bound to slow down db operations, complicate code etc. are required.
This is the sort of place where Modx limits itself. Such a great environment in so many ways, and things like this act as barriers to adoption. I’ve been teetering on the brink of abandoning it for a year because of complications that arise from this.
I tried to resolve the issue using the method recommended by several in the community -- an elaborate @EVAL in one tv that reads another and writes a value to a third. But this causes grief when updates and duplications happen, which turn out to be important to the uses my client is putting their ModX based site.
That said, the transfer from one hosting provider to another has provided the occasion to attack some of this cruft. Given the need to have a unixtime string stored whenever the user updates a date tv, instead of the old method, I’ve hacked the save_content.processor.php file. The results are much more satisfactory, and require no db mods, so I’m posting them here in case someone else can benefit from the approach.
The bit of code I’ve posted below does the following:
- after any tv updates or new values are written to the db in save_content.processor.php, it scans the _site_tmplvars table for any tvs whose names end in ’_daterow’.
- it then looks for a tv with the same name, minus the ’_daterow’. So, you might have a tv called ’showStart’, and if you have another tv called ’showStart_daterow’ it’ll find the former.
- it then looks for a db entry for the current page for the source tv, ie ’showStart’ in my example.
- it extracts the value, assuming it’s a date formatted as modx stores them with the date widget.
- it converts this to a unixtime string, and stores it in a ’_daterow’ tv entry for the current page, creating one if one does not exist.
What all this means is that if you add this to save_content.processor.php, starting at around line 473, (check for code matches at the beginning and end of the code below in your copy), to store unixtime value semi-automatically, you just need to:
- create a tv with the date widget for input;
- create a second, with the same name, and the suffix ’_daterow’.
Now you can use that second field to sort, search and compare entries.
Hope that this is useful to someone.
Needs more error correction, but I assume that this will get reworked anyway....
-- removed some inadvertently included test code... DD
MODX Staff
MODX Staff
