In a resource I call a snippet (see below) to check whether other resources have the same longtitle and if so ORDER BY the value of a TV called ’released_date’. The problem is that ORDER BY has to be a column name but TVs are not stored as a column name. I’ve joined the modx_site_content and modx tv tables but still cannot work out how to get the TV ’release_date’ value. Any suggestions would be appreciated.
<?php
$thisid = $modx->documentIdentifier; // get current resource id
$long_title = $modx->documentObject['longtitle']; // get resource longtitle
$lt = mysql_real_escape_string($long_title);
$result = $modx->db->query("
SELECT longtitle, pagetitle, modx_site_content.id AS cid,
FROM modx_site_content
LEFT JOIN modx_site_tmplvar_contentvalues ON modx_site_content.id = contentid
LEFT JOIN modx_site_tmplvars ON modx_site_tmplvar_contentvalues.tmplvarid = modx_site_tmplvars.id
WHERE longtitle = '$lt' AND modx_site_content.id != $thisid AND parent IN (21,23,272)
ORDER BY released_year ASC
");
if ($modx->db->getRecordCount($result) >= 1) {
while( $row = $modx->db->getRow($result)) {
$list .='<li><a href="?id=' . $row[cid] . '">' . $row[pagetitle] . </a></li>'; }
$output .= '<p>Other Editions:</p><ul>' . $list . '</ul>';
} else {
$output = ''; }
return $output;
?>
