Here’s the snippet I’ve been using. Even if it doesn’t do exactly what you want, you may be able to adapt it to your needs.
<?php
//This snippet lists records alphabetically, with an A to Z header of links to anchors in the text.
//Can sort by any content field, but not by template variables.
//Can filter by any content field or template variable.
//simple use:
//[!atozList? &parents=`6` &listTpl=`atozStaff`!]
//Required fields: &parents and &listTpl
//Optional fields: &sortby &alphabetHeadingSeparator &alphabetHeadingStart &alphabetHeadingEnd &filter &noResults &excludeDocuments
$alphabet = range("A", "Z");
//Sets the field to sort by. Can't currently use template variables.
$atozSortBy = (isset($sortby)) ? stripslashes($sortby) : 'pagetitle';
//Separate the a-to-z headings.
$atozAlphabetHeadingSeparator = (isset($alphabetHeadingSeparator)) ? $alphabetHeadingSeparator : ' | ';
//a-to-z headings start
$atozAlphabetHeadingStart = (isset($alphabetHeadingStart)) ? $alphabetHeadingStart : '<p>';
//a-to-z headings end
$atozAlphabetHeadingEnd = (isset($alphabetHeadingEnd)) ? $alphabetHeadingEnd : '</p>';
//a-to-z list chunk template. Ditto template
$atozListChunkTpl = (isset($listTpl)) ? $listTpl : '';
//ids of subfolders whose documents will be processed.
$parents = (isset ($parents)) ? stripslashes($parents) : '';
//A single basic ditto filter to exclude.
$filter = (isset ($filter)) ? stripslashes($filter) : '';
//Message to return if no results.
$noResults = (isset ($noResults)) ? stripslashes($noResults) : '';
//A list of documents or folders to exclude (folders are included by default), separated by commas
$excludeDocuments = (isset ($excludeDocuments)) ? stripslashes($excludeDocuments) : '';
//If parent folders are defined, continue. Otherwise, do nothing at all!
if (trim($parents) != '') {
//This is the table that contains the document information
$content_table = $modx->getFullTableName("site_content");
//This is the table that contains the values of template variables
$tv_content_table = $modx->getFullTableName("site_tmplvar_contentvalues");
//This is the table that contains the actual template variables
$tv_table = $modx->getFullTableName("site_tmplvars");
if(!empty($atozSortBy)) {
if(!empty($filter)) {
//Find all the field names in the site_content table, so we can identify whether filter relates to a content field or a template variable.
$fields_result = $modx->db->query("SELECT * FROM ". $content_table);
$numberfields = mysql_num_fields($fields_result);
$content_field=FALSE;
for ($i=0; $i<$numberfields ; $i++ ) {
if(trim($filter)==mysql_field_name($fields_result, $i)){
$content_field=TRUE;
}
}
$filterArray = explode(',', $filter);
$filterValue = $filterArray[1];
if(trim($filterArray[2])=='1') {
$condition='=';
}elseif(trim($filterArray[2])=='2') {
$condition='!=';
}elseif(trim($filterArray[2])=='3') {
$condition='>=';
}elseif(trim($filterArray[2])=='4') {
$condition='<=';
}elseif(trim($filterArray[2])=='5') {
$condition='>';
}elseif(trim($filterArray[2])=='6') {
$condition='<';
}elseif(trim($filterArray[2])=='7') {
$condition=' LIKE ';
$filterValue='%'.$filterValue.'%';
}elseif(trim($filterArray[2])=='8') {
$condition=' NOT LIKE ';
$filterValue='%'.$filterValue.'%';
}else{
$condition='='; //Default to equals, to avoid errors
}
$filterValue = "'".$filterValue."'";
}
}
//First we are going to make a list of parent folders for a MySQL WHERE
//split out the parent values
$startArray = explode(',', $parents);
//loop through each start id and concatenate to list parent folders for database query
$parent_folders = '';
$parent_folders_for_tvquery = '';
foreach ($startArray as $parentid) {
$parent_folders .= 'parent=' . $parentid . ' OR ';
$parent_folders_for_tvquery .= $content_table . '.parent=' . $parentid . ' OR ';
}
$parent_folders = substr($parent_folders, 0, -4);
$parent_folders_for_tvquery = substr($parent_folders_for_tvquery, 0, -4);
$parent_folders_where = '(' . $parent_folders . ')';
//Now we are going to make a list of documents that will be excluded for a MySQL WHERE
//split out the document values
$startDocumentArray = explode(',', $excludeDocuments);
$exclude_documents = '';
$exclude_documents_for_tvquery = '';
$exclude_documents_where = '';
$exclude_documents_for_tvquery_where = '';
//We want to also add the individual documents to the overall ditto filter at the end
$ditto_filter = '';
if(!empty($excludeDocuments)){
if(!empty($filter)){
$ditto_filter .= $filter . '|';
}
foreach ($startDocumentArray as $documentid) {
$exclude_documents .= 'id!=' . $documentid . ' AND ';
$exclude_documents_for_tvquery .= $content_table . '.id!=' . $documentid . ' AND ';
$ditto_filter .= 'id,' . $documentid . ',2|';
}
$exclude_documents = substr($exclude_documents, 0, -5);
$exclude_documents_for_tvquery = substr($exclude_documents_for_tvquery, 0, -5);
$exclude_documents_for_tvquery_where = ' AND (' . $exclude_documents_for_tvquery . ')';
$exclude_documents_where = ' AND (' . $exclude_documents . ')';
$ditto_filter = substr($ditto_filter, 0, -1);
} else {
$ditto_filter = $filter;
}
//Ensure only published documents are displayed. Ensure that folders are displayed by default
$conditional_where = ' AND published=1 AND deleted=0 AND privateweb=0';
//Select the pagetitles to have records to count
$content_select = 'pagetitle';
$documentId = $modx->documentIdentifier;
$alphabetHeadings = $atozAlphabetHeadingStart;
$output = '';
foreach($alphabet AS $value) {
//The where statement for the ditto call
$where = $atozSortBy . " LIKE '" . $value . "%'";
//Want to check if there are any records in the database that match, before we start the ditto call.
//First check if there is a filter. If so, see if the filter is on a content field or a template variable.
if(!empty($filter)){
if($content_field){
$full_where = $parent_folders_where . $exclude_documents_where . $conditional_where . " AND " . $where . " AND " . $filterArray[0] . $condition . $filterValue;
$contentResult = $modx->db->select($content_select, $content_table, $full_where, '', '');
$total_rows = $modx->db->getRecordCount( $contentResult );
} else {
//Query to see if there are any values of template variables that match the filter.
$total_rows = 0;
$select = $tv_content_table . ".`contentid` AS contentid";
$from = $tv_content_table . ", " . $tv_table . ", " . $content_table;
$full_where = $content_table . '.`id`=' . $tv_content_table . '.`contentid` AND ' . $tv_table . ".`id`=" . $tv_content_table . ".`tmplvarid` AND value" . $condition . $filterValue . " AND name='" . $filterArray[0] . "' AND " . $where . " AND (" . $parent_folders_for_tvquery . ")" . $exclude_documents_for_tvquery_where . $conditional_where;
$tvResult = $modx->db->select($select, $from, $full_where, '', '');
$number_rows = $modx->db->getRecordCount( $tvResult );
$document_where = "";
if($number_rows>0){
//If there are values of tvs that match the filter, see if there are any results that fit the appropriate A-Z
while($row=$modx->db->getRow($tvResult)) {
$document_where .= $content_table .".`id`=" . $row['contentid'] . " OR ";
}
$document_where = substr($document_where, 0, -4);
$full_where = $parent_folders . $conditional_where . " AND " . $where . " AND " . $document_where;
$contentResult = $modx->db->select($content_select, $content_table, $full_where, '', '');
$total_rows = $modx->db->getRecordCount( $contentResult );
}
}
}else{
$full_where = $parent_folders_where . $exclude_documents_where . $conditional_where . " AND " . $where;
$contentResult = $modx->db->select($content_select, $content_table, $full_where, '', '');
$total_rows = $modx->db->getRecordCount( $contentResult );
}
if($total_rows > 0) {
//Update the A-Z link list at top of page
$alphabetHeadings .= '<a href="'.$modx->makeUrl($documentId).'#jump_to_' . $value . '">' . $value . '</a>';
$alphabetHeadings .= $atozAlphabetHeadingSeparator;
$output .= "<p><a name=\"jump_to_$value\" id=\"jump_to_$value\"></a><strong>" . $value . "</strong></p>";
$output .= $modx->runSnippet("Ditto",array("parents" => "$parents", "display" => "all", "sortBy" => "$atozSortBy", "sortDir" => "ASC", "filter" => "$ditto_filter", "tpl" => "$atozListChunkTpl", "hideFolders" => "0", "where" => "$where"));
}
}
$separatorCount = strlen($atozAlphabetHeadingSeparator);
$alphabetHeadings = substr($alphabetHeadings, 0, -$separatorCount);
$alphabetHeadings .= $atozAlphabetHeadingEnd;
if($output) {
$output = $alphabetHeadings . $output;
return $output;
} else {
return $noResults;
}
}
?>
