So, after much experimentation, here is my inelegant solution to sorting by a date other than the standard ones.
This relies (in my solution) on 3 tvs:
- a date tv that I use to enter dates with (these are dates a show will happen);
- a processing tv that has an @EVAL statement in its default value;
- a storage tv that the processing tv writes a number into (a unixtime value).
Here is the code that is the EVAL statement for the processing tv:
@EVAL
// these are the names of the tvs with the date, and that we use to store the unixtime
$dateTV = "showDate";
$storageTV = "unixtime";
// get the data for the date that has been set for this event
$rslt = $modx->getTemplateVar($dateTV,"",$modx->documentObject['id'],true);
$utime = $rslt['value'];
// it's a mysql date formatted string, take it apart
$date = explode(" ",$utime);
$d = explode("-",$date[0]);
$t = explode(":",$date[1]);
// we can manipulate these elements: for instance, we can change the time values to midnight, if needed
$tstamp = mktime($t[0],$t[1],$t[2],$d[1],$d[0],$d[2]);
// since this only renders on the event page, we can refer to the modx documentObject
$myId = $modx->documentObject['id'];
// should really alter this to use $modx's table name methods, but in the meantime its late...
$tbl1 = "modx_site_tmplvar_contentvalues";
$tbl2 = "modx_site_tmplvars";
$sql = "SELECT $tbl1.id FROM $tbl1, $tbl2 WHERE ";
$sql.= "$tbl1.contentid = '$myId' AND $tbl1.tmplvarid = $tbl2.id AND $tbl2.name = '$storageTV' LIMIT 1";
// lets find out if there's an existing unixtime record for this event
$rslt = $modx->dbQuery($sql);
if($modx->recordCount($rslt)){
// yes, there is, so get its id, and use that to update the record
$tvId = $modx->fetchRow($rslt);
$tvId = $tvId['id'];
$sql = "UPDATE $tbl1 SET value = '$tstamp' WHERE id='$tvId'";
$modx->dbQuery($sql);
} else {
// no, there isn't so lets get the id for the tv we're storing in
$sql = "SELECT id FROM $tbl2 WHERE name='$storageTV'";
$rslt = $modx->dbQuery($sql);
// should really add some error checking here
$tmplvarid = $modx->fetchRow($rslt);
$tmplvarid = $tmplvarid['id'];
// we have all the data, now create the new record
$sql = "INSERT INTO $tbl1 SET value='$tstamp', tmplvarid='$tmplvarid', contentid='$myId'";
$modx->dbQuery($sql);
}
return "$tstamp";
Basically, what happens is this:
- the user sets the date using the first tv. This is a date type, with a date formatter on it.
- when the value is set, and the new value stored, the page is refreshed.
- this causes as re-evaluation of the second (processing) tv. It is important that you have this tv in the page for the event. But it is also necessary that you
not have it in the page where the NewsListing is. This is because the line
@EVAL
...
$rslt = $modx->getTemplateVar($dateTV,"",$modx->documentObject['id'],true);
would use the id of the wrong page, and produce an incorrect result (usually -1). When the EVAL happens, the php checks to see if there is an existing record for the storage tv. This tv (the storage one) doesn’t need to be on any page AFAIK. In any event, the php calculates the unixtime from the data in the first, date tv and stores it, as a number, in the storage tv. If there isn’t a record existing, it creates it. If there is, it updates it. You don’t want to make any of these tvs, except probably the date one, editable by anyone.
In the NewsListing call, ensure that you include the parameters
[[NewsListing? &sortby=`tvunixtime` &sortdir=`asc`]]
don’t forget the sortdir, as the default will be in the wrong direction for most uses - this is so we can show things coming in the future, so the order is reversed from most blog entries.
Still to do is a filter for NewsListing to ensure that the first listing is the next one in the future.
Hope that this is useful to someone.
