/**
* @param $pa_hits Array of row_ids to sort. *MUST HAVE row_ids AS KEYS, NOT VALUES*
*/
public function sortHits($pa_hits, $ps_field, $ps_direction = 'asc', $pa_options = null)
{
if (!in_array(strtolower($ps_direction), array('asc', 'desc'))) {
$ps_direction = 'asc';
}
if (!is_array($pa_hits) || !sizeof($pa_hits)) {
return $pa_hits;
}
$vs_search_tmp_table = $this->loadListIntoTemporaryResultTable($pa_hits, $pa_options['search']);
$t_table = $this->opo_datamodel->getInstanceByTableNum($this->opn_tablenum, true);
$vs_table_pk = $t_table->primaryKey();
$vs_table_name = $this->ops_tablename;
$va_fields = explode(';', $ps_field);
$va_sorted_hits = array();
$vn_num_locales = ca_locales::numberOfCataloguingLocales();
foreach ($va_fields as $vs_field) {
$va_joins = $va_orderbys = array();
$vs_locale_where = $vs_is_preferred_sql = '';
$va_tmp = explode('.', $vs_field);
// Rewrite for <table>.preferred_labels.* syntax
if ($va_tmp[1] == 'preferred_labels') {
if ($t_labeled_item_table = $this->opo_datamodel->getInstanceByTableName($va_tmp[0], true)) {
if ($t_label_table = $t_labeled_item_table->getLabelTableInstance()) {
$va_tmp2 = array($t_label_table->tableName());
if (isset($va_tmp[2]) && $t_label_table->hasField($va_tmp[2])) {
$va_tmp2[] = $va_tmp[2];
} else {
$va_tmp2[] = $t_labeled_item_table->getLabelDisplayField();
}
$va_tmp = $va_tmp2;
$vs_field = join(".", $va_tmp);
}
}
}
if ($va_tmp[0] == $vs_table_name) {
//
// sort field is in search table
//
if (!$t_table->hasField($va_tmp[1])) {
//
// is it an attribute?
//
$t_element = new ca_metadata_elements();
$vs_sort_element_code = array_pop($va_tmp);
if ($t_element->load(array('element_code' => $vs_sort_element_code))) {
$vn_element_id = $t_element->getPrimaryKey();
if (!($vs_sortable_value_fld = Attribute::getSortFieldForDatatype($t_element->get('datatype')))) {
return $pa_hits;
}
if ((int) $t_element->get('datatype') == 3) {
$vs_sortable_value_fld = 'lil.name_plural';
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_locale_where = $vn_num_locales > 1 ? ', lil.locale_id' : '';
$vs_sql = "\n\t\t\t\t\t\t\t\tSELECT attr.row_id, lil.locale_id, lower({$vs_sortable_value_fld}) {$vs_sort_field}\n\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values AS attr_vals ON attr_vals.attribute_id = attr.attribute_id\n\t\t\t\t\t\t\t\tINNER JOIN ca_list_item_labels AS lil ON lil.item_id = attr_vals.item_id\n\t\t\t\t\t\t\t\tINNER JOIN {$vs_browse_tmp_table} ON {$vs_browse_tmp_table}.row_id = attr.row_id\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND (attr.table_num = ?) AND (lil.{$vs_sort_field} IS NOT NULL)\n\t\t\t\t\t\t\t\tORDER BY lil.{$vs_sort_field}\n\t\t\t\t\t\t\t";
} else {
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_locale_where = $vn_num_locales > 1 ? 'attr.locale_id' : '';
$vs_sql = "\n\t\t\t\t\t\t\t\tSELECT attr.row_id, attr.locale_id, lower({$vs_sortable_value_fld}) {$vs_sort_field}\n\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values AS attr_vals ON attr_vals.attribute_id = attr.attribute_id\n\t\t\t\t\t\t\t\tINNER JOIN {$vs_search_tmp_table} ON {$vs_search_tmp_table}.row_id = attr.row_id\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND (attr.table_num = ?) AND (attr_vals.{$vs_sort_field} IS NOT NULL)\n\t\t\t\t\t\t\t\tORDER BY attr_vals.{$vs_sort_field}\n\t\t\t\t\t\t\t";
//print $vs_sql." ; $vn_element_id/; ".$this->opn_tablenum."<br>";
}
$qr_sort = $this->opo_db->query($vs_sql, (int) $vn_element_id, (int) $this->opn_tablenum);
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
if (!$va_row['row_id']) {
continue;
}
if ($vn_num_locales > 1) {
$va_sorted_hits[$va_row['row_id']][$va_row['locale_id']] .= trim(str_replace(array("'", '"'), array('', ''), $va_row[$vs_sort_field]));
} else {
$va_sorted_hits[$va_row['row_id']] .= trim(str_replace(array("'", '"'), array('', ''), $va_row[$vs_sort_field]));
}
unset($pa_hits[$va_row['row_id']]);
}
// Add on hits that aren't sorted because they don't have an attribute associated
foreach ($pa_hits as $vn_id => $va_row) {
if (!is_array($va_row)) {
$va_row = array();
}
if ($vn_num_locales > 1) {
$va_sorted_hits[$vn_id][1] = $va_row;
} else {
$va_sorted_hits[$vn_id] = $va_row;
}
}
}
continue;
} else {
$va_field_info = $t_table->getFieldInfo($va_tmp[1]);
if ($va_field_info['START'] && $va_field_info['END']) {
$va_orderbys[] = $va_field_info['START'] . ' ' . $ps_direction;
$va_orderbys[] = $va_field_info['END'] . ' ' . $ps_direction;
} else {
$va_orderbys[] = $vs_field . ' ' . $ps_direction;
}
if ($t_table->hasField('locale_id')) {
$vs_locale_where = ", " . $vs_table_name . ".locale_id";
}
$vs_sortable_value_fld = $vs_field;
}
} else {
// sort field is in related table
$va_path = $this->opo_datamodel->getPath($vs_table_name, $va_tmp[0]);
if (sizeof($va_path) > 2) {
// many-many
$vs_last_table = null;
// generate related joins
foreach ($va_path as $vs_table => $va_info) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if (!$vs_last_table) {
//$va_joins[$vs_table] = "INNER JOIN ".$vs_table." ON ".$vs_table.".".$t_table->primaryKey()." = ca_sql_search_search_final.row_id";
} else {
$va_rels = $this->opo_datamodel->getOneToManyRelations($vs_last_table, $vs_table);
if (!sizeof($va_rels)) {
$va_rels = $this->opo_datamodel->getOneToManyRelations($vs_table, $vs_last_table);
}
if ($vs_table == $va_rels['one_table']) {
$va_joins[$vs_table] = "INNER JOIN " . $va_rels['one_table'] . " ON " . $va_rels['one_table'] . "." . $va_rels['one_table_field'] . " = " . $va_rels['many_table'] . "." . $va_rels['many_table_field'];
} else {
$va_joins[$vs_table] = "INNER JOIN " . $va_rels['many_table'] . " ON " . $va_rels['many_table'] . "." . $va_rels['many_table_field'] . " = " . $va_rels['one_table'] . "." . $va_rels['one_table_field'];
}
}
$t_last_table = $t_table;
$vs_last_table = $vs_table;
}
$va_orderbys[] = $vs_field . ' ' . $ps_direction;
$vs_sortable_value_fld = $vs_field;
} else {
$va_rels = $this->opo_datamodel->getRelationships($vs_table_name, $va_tmp[0]);
if (!$va_rels) {
return $pa_hits;
}
// return hits unsorted if field is not valid
$t_rel = $this->opo_datamodel->getInstanceByTableName($va_tmp[0], true);
if (!$t_rel->hasField($va_tmp[1])) {
return $pa_hits;
}
$va_joins[$va_tmp[0]] = 'LEFT JOIN ' . $va_tmp[0] . ' ON ' . $vs_table_name . '.' . $va_rels[$vs_table_name][$va_tmp[0]][0][0] . ' = ' . $va_tmp[0] . '.' . $va_rels[$vs_table_name][$va_tmp[0]][0][1] . "\n";
$va_orderbys[] = $vs_field . ' ' . $ps_direction;
// if the related supports preferred values (eg. *_labels tables) then only consider those in the sort
if ($t_rel->hasField('is_preferred')) {
$vs_is_preferred_sql = " " . $va_tmp[0] . ".is_preferred = 1";
}
if ($t_rel->hasField('locale_id')) {
$vs_locale_where = ", " . $va_tmp[0] . ".locale_id";
}
$vs_sortable_value_fld = $vs_field;
}
}
//
// Grab values and index for sorting later
//
$va_tmp = explode('.', $vs_sortable_value_fld);
$vs_sort_field = array_pop($va_tmp);
$vs_join_sql = join("\n", $va_joins);
$vs_sql = "\n\t\t\t\tSELECT {$vs_table_name}.{$vs_table_pk}{$vs_locale_where}, lower({$vs_sortable_value_fld}) {$vs_sort_field}\n\t\t\t\tFROM {$vs_table_name}\n\t\t\t\t{$vs_join_sql}\n\t\t\t\tINNER JOIN {$vs_search_tmp_table} ON {$vs_search_tmp_table}.row_id = {$vs_table_name}.{$vs_table_pk}\n\t\t\t\t" . ($vs_is_preferred_sql ? 'WHERE' : '') . "\n\t\t\t\t\t{$vs_is_preferred_sql}\n\t\t\t";
//print $vs_sql;
$qr_sort = $this->opo_db->query($vs_sql);
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
if (!($vs_sortable_value = str_replace(array("'", '"'), array('', ''), $va_row[$vs_sort_field]))) {
$vs_sortable_value = '';
}
if ($vn_num_locales > 1 && $vs_locale_where) {
$va_sorted_hits[$va_row[$vs_table_pk]][$va_row['locale_id']] .= $vs_sortable_value;
} else {
$va_sorted_hits[$va_row[$vs_table_pk]] .= $vs_sortable_value;
}
}
}
//
// Actually sort the hits here...
//
if ($vn_num_locales > 1 && $vs_locale_where) {
$va_sorted_hits = caExtractValuesByUserLocale($va_sorted_hits);
}
asort($va_sorted_hits, SORT_STRING);
if ($ps_direction == 'desc') {
$va_sorted_hits = array_reverse($va_sorted_hits, true);
}
$this->cleanupTemporaryResultTable();
return $va_sorted_hits;
}
