/**
* Find row(s) with fields having values matching specific values.
* Results can be returned as model instances, numeric ids or search results (when possible).
*
* Exact matching is performed using values in $pa_values. Partial and pattern matching are not supported. Searches may include
* multiple fields with boolean AND and OR. For example, you can find ca_objects rows with idno = 2012.001 and access = 1 by passing the
* "boolean" option as "AND" and $pa_values set to array("idno" => "2012.001", "access" => 1).
* You could find all rows with either the idno or the access values by setting "boolean" to "OR"
*
* Keys in the $pa_values parameters must be valid fields in the table which the model sub-class represents. You may also search on preferred and
* non-preferred labels by specified keys and values for label table fields in "preferred_labels" and "nonpreferred_labels" sub-arrays. For example:
*
* array("idno" => 2012.001", "access" => 1, "preferred_labels" => array("name" => "Luna Park at Night"))
*
* will find rows with the idno, access and preferred label values.
*
* LabelableBaseModelWithAttributes::find() is not a replacement for the SearchEngine. It is intended as a quick and convenient way to programatically fetch rows using
* simple, clear cut criteria. If you need to fetch rows based upon an identifer or status value LabelableBaseModelWithAttributes::find() will be quicker and less code than
* using the SearchEngine. For full-text searches, searches on attributes, or searches that require transformations or complex boolean operations use
* the SearchEngine.
*
* @param array $pa_values An array of values to match. Keys are field names, metadata element codes or preferred_labels and /or nonpreferred_labels. This must be an array with at least one key-value pair where the key is a valid field name for the model.
* @param array $pa_options Options are:
* transaction = optional Transaction instance. If set then all database access is done within the context of the transaction
* returnAs = what to return; possible values are:
* searchResult = a search result instance (aka. a subclass of BaseSearchResult), when the calling subclass is searchable (ie. <classname>Search and <classname>SearchResult classes are defined)
* ids = an array of ids (aka. primary keys)
* modelInstances = an array of instances, one for each match. Each instance is the same class as the caller, a subclass of BaseModel
* firstId = the id (primary key) of the first match. This is the same as the first item in the array returned by 'ids'
* firstModelInstance = the instance of the first match. This is the same as the first instance in the array returned by 'modelInstances'
* count = the number of matches
*
* The default is ids
*
* limit = if searchResult, ids or modelInstances is set, limits number of returned matches. Default is no limit
* boolean = determines how multiple field values in $pa_values are combined to produce the final result. Possible values are:
* AND = find rows that match all criteria in $pa_values
* OR = find rows that match any criteria in $pa_values
*
* The default is AND
*
* labelBoolean = determines how multiple field values in $pa_values['preferred_labels'] and $pa_values['nonpreferred_labels'] are combined to produce the final result. Possible values are:
* AND = find rows that match all criteria in $pa_values['preferred_labels']/$pa_values['nonpreferred_labels']
* OR = find rows that match any criteria in $pa_values['preferred_labels']/$pa_values['nonpreferred_labels']
*
* The default is AND
*
* sort = field to sort on. Must be in <table>.<field> format and be an intrinsic field in either the primary table or the label table. Sort order can be set using the sortDirection option.
* sortDirection = the direction of the sort. Values are ASC (ascending) and DESC (descending). [Default is ASC]
* allowWildcards = consider "%" as a wildcard when searching. Any term including a "%" character will be queried using the SQL LIKE operator. [Default is false]
*
* @return mixed Depending upon the returnAs option setting, an array, subclass of LabelableBaseModelWithAttributes or integer may be returned.
*/
public static function find($pa_values, $pa_options = null)
{
if (!is_array($pa_values) || sizeof($pa_values) == 0) {
return null;
}
$ps_return_as = caGetOption('returnAs', $pa_options, 'ids', array('forceLowercase' => true, 'validValues' => array('searchResult', 'ids', 'modelInstances', 'firstId', 'firstModelInstance', 'count')));
$ps_boolean = caGetOption('boolean', $pa_options, 'and', array('forceLowercase' => true, 'validValues' => array('and', 'or')));
$ps_label_boolean = caGetOption('labelBoolean', $pa_options, 'and', array('forceLowercase' => true, 'validValues' => array('and', 'or')));
$ps_sort = caGetOption('sort', $pa_options, null);
$vs_table = get_called_class();
$t_instance = new $vs_table();
$vn_table_num = $t_instance->tableNum();
$vs_table_pk = $t_instance->primaryKey();
$t_label = $t_instance->getLabelTableInstance();
$vs_label_table = $t_label->tableName();
$vs_label_table_pk = $t_label->primaryKey();
$vb_has_simple_fields = false;
foreach ($pa_values as $vs_field => $vm_value) {
if (!is_array($vm_value) && $t_instance->hasField($vs_field)) {
$vb_has_simple_fields = true;
break;
}
}
$vb_has_label_fields = false;
foreach ($pa_values as $vs_field => $vm_value) {
if (in_array($vs_field, array('preferred_labels', 'nonpreferred_labels')) && is_array($vm_value) && sizeof($vm_value)) {
$vb_has_label_fields = true;
break;
}
}
$vs_sort_proc = $ps_sort;
if (preg_match("!^{$vs_table}.preferred_labels[\\.]{0,1}(.*)!", $ps_sort, $va_matches) || preg_match("!^{$vs_table}.nonpreferred_labels[\\.]{0,1}(.*)!", $ps_sort, $va_matches)) {
$vs_sort_proc = $va_matches[1] && $t_label->hasField($va_matches[1]) ? "{$vs_label_table}." . $va_matches[1] : "{$vs_label_table}." . $t_label->getDisplayField();
$vb_has_label_fields = true;
}
$vb_has_attributes = false;
$va_element_codes = $t_instance->getApplicableElementCodes(null, true, false);
foreach ($pa_values as $vs_field => $vm_value) {
if (!is_array($vm_value) && in_array($vs_field, $va_element_codes)) {
$vb_has_attributes = true;
break;
}
}
$va_joins = array();
$va_sql_params = array();
if ($vb_has_simple_fields) {
//
// Convert type id
//
if ($t_instance->ATTRIBUTE_TYPE_LIST_CODE) {
if (isset($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD]) && !is_numeric($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD])) {
if (!is_array($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD])) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] = array($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD]);
}
foreach ($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] as $vn_i => $vm_value) {
if (!is_numeric($vm_value)) {
if ($vn_id = ca_lists::getItemID($t_instance->ATTRIBUTE_TYPE_LIST_CODE, $vm_value)) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD][$vn_i] = $vn_id;
}
}
}
}
}
//
// Convert other intrinsic list references
//
foreach ($pa_values as $vs_field => $vm_value) {
if ($vs_field == $t_instance->ATTRIBUTE_TYPE_ID_FLD) {
continue;
}
if ($vs_list_code = $t_instance->getFieldInfo($vs_field, 'LIST_CODE')) {
if (!is_array($vm_value)) {
$pa_values[$vs_field] = $vm_value = array($vm_value);
}
foreach ($vm_value as $vn_i => $vm_ivalue) {
if (is_numeric($vm_ivalue)) {
continue;
}
if ($vn_id = ca_lists::getItemID($vs_list_code, $vm_ivalue)) {
$pa_values[$vs_field][$vn_i] = $vn_id;
}
}
}
}
}
$va_sql_wheres = array();
if ($vb_has_simple_fields && !$vb_has_attributes && !$vb_has_label_fields) {
return parent::find($pa_values, $pa_options);
}
$va_label_sql = array();
if ($vb_has_label_fields) {
$va_joins[] = " INNER JOIN {$vs_label_table} ON {$vs_label_table}.{$vs_table_pk} = {$vs_table}.{$vs_table_pk} ";
if (isset($pa_values['preferred_labels']) && is_array($pa_values['preferred_labels'])) {
$va_sql_wheres[] = "({$vs_label_table}.is_preferred = 1)";
foreach ($pa_values['preferred_labels'] as $vs_field => $vm_value) {
if (!$t_label->hasField($vs_field)) {
return false;
}
if ($t_label->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
$vm_value = intval($vm_value);
}
} else {
$vm_value = $t_label->quote($vs_field, is_null($vm_value) ? '' : $vm_value);
}
if (is_null($vm_value)) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} IS NULL)";
} elseif (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} LIKE {$vm_value})";
} else {
if ($vm_value === '') {
continue;
}
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} = {$vm_value})";
}
}
$va_label_sql[] = "(" . join(" {$ps_label_boolean} ", $va_sql_wheres) . ")";
$va_sql_wheres = array();
}
if (isset($pa_values['nonpreferred_labels']) && is_array($pa_values['nonpreferred_labels'])) {
$va_sql_wheres[] = "({$vs_label_table}.is_preferred = 0)";
foreach ($pa_values['nonpreferred_labels'] as $vs_field => $vm_value) {
if (!$t_label->hasField($vs_field)) {
return false;
}
if ($t_label->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
$vm_value = intval($vm_value);
}
} else {
$vm_value = $t_label->quote($vs_field, is_null($vm_value) ? '' : $vm_value);
}
if (is_null($vm_value)) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} IS NULL)";
} else {
if ($vm_value === '') {
continue;
}
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} = {$vm_value})";
}
}
$va_label_sql[] = "(" . join(" {$ps_label_boolean} ", $va_sql_wheres) . ")";
$va_sql_wheres = array();
}
}
if ($vb_has_simple_fields) {
foreach ($pa_values as $vs_field => $vm_value) {
//if (is_array($vm_value)) { continue; }
if (!$t_instance->hasField($vs_field)) {
continue;
}
if ($t_instance->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
if (is_array($vm_value)) {
foreach ($vm_value as $vn_i => $vm_ivalue) {
$vm_value[$vn_i] = intval($vm_ivalue);
}
} else {
$vm_value = intval($vm_value);
}
}
}
if (is_null($vm_value)) {
$va_label_sql[] = "({$vs_table}.{$vs_field} IS NULL)";
} elseif (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$va_label_sql[] = "({$vs_table}.{$vs_field} LIKE ?)";
$va_sql_params[] = $vm_value;
} else {
if ($vm_value === '') {
continue;
}
if (is_array($vm_value)) {
if (!sizeof($vm_value)) {
continue;
}
$va_label_sql[] = "({$vs_table}.{$vs_field} IN (?))";
} else {
$va_label_sql[] = "({$vs_table}.{$vs_field} = ?)";
}
$va_sql_params[] = $vm_value;
}
}
}
if ($vb_has_attributes) {
$va_joins[] = " INNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_table}.{$vs_table_pk} AND ca_attributes.table_num = {$vn_table_num} ";
$va_joins[] = " INNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id ";
foreach ($pa_values as $vs_field => $vm_value) {
if (($vn_element_id = array_search($vs_field, $va_element_codes)) !== false) {
$vs_q = " (ca_attribute_values.element_id = {$vn_element_id}) AND ";
switch ($vn_datatype = $t_instance->_getElementDatatype($vs_field)) {
case 0:
// continue
// continue
case 15:
// media
// media
case 16:
// file
// SKIP
continue 2;
break;
case 2:
// date
if (is_array($va_date = caDateToHistoricTimestamps($vm_value))) {
$vs_q .= "((ca_attribute_values.value_decimal1 BETWEEN ? AND ?) OR (ca_attribute_values.value_decimal2 BETWEEN ? AND ?))";
array_push($va_sql_params, $va_date['start'], $va_date['end'], $va_date['start'], $va_date['end']);
} else {
continue 2;
}
break;
case 3:
// list
$vn_item_id = is_numeric($vm_value) ? (int) $vm_value : (int) caGetListItemID($vm_value);
$vs_q .= "(ca_attribute_values.item_id = ?)";
$va_sql_params[] = $vn_item_id;
break;
default:
if (!($vs_fld = Attribute::getSortFieldForDatatype($vn_datatype))) {
$vs_fld = 'value_longtext1';
}
if (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$vs_q .= "(ca_attribute_values.{$vs_fld} LIKE ?)";
} else {
$vs_q .= "(ca_attribute_values.{$vs_fld} = ?)";
}
$va_sql_params[] = (string) $vm_value;
break;
}
$va_label_sql[] = "({$vs_q})";
}
}
}
if (!sizeof($va_label_sql)) {
return null;
}
$vs_deleted_sql = $t_instance->hasField('deleted') ? "({$vs_table}.deleted = 0) AND " : '';
$vs_sql = "SELECT * FROM {$vs_table}";
$vs_sql .= join("\n", $va_joins);
$vs_sql .= " WHERE {$vs_deleted_sql} " . join(" {$ps_boolean} ", $va_label_sql);
$vs_orderby = '';
if ($vs_sort_proc) {
$vs_sort_direction = caGetOption('sortDirection', $pa_options, 'ASC', array('validValues' => array('ASC', 'DESC')));
$va_tmp = explode(".", $vs_sort_proc);
if (sizeof($va_tmp) == 2) {
switch ($va_tmp[0]) {
case $vs_table:
if ($t_instance->hasField($va_tmp[1])) {
$vs_orderby = " ORDER BY {$vs_sort_proc} {$vs_sort_direction}";
}
break;
case $vs_label_table:
if ($t_label->hasField($va_tmp[1])) {
$vs_orderby = " ORDER BY {$vs_sort_proc} {$vs_sort_direction}";
}
break;
}
}
if ($vs_orderby) {
$vs_sql .= $vs_orderby;
}
}
if (isset($pa_options['transaction']) && $pa_options['transaction'] instanceof Transaction) {
$o_db = $pa_options['transaction']->getDb();
} else {
$o_db = new Db();
}
$vn_limit = isset($pa_options['limit']) && (int) $pa_options['limit'] > 0 ? (int) $pa_options['limit'] : null;
$qr_res = $o_db->query($vs_sql, $va_sql_params);
$vn_c = 0;
$vs_pk = $t_instance->primaryKey();
switch ($ps_return_as) {
case 'firstmodelinstance':
while ($qr_res->nextRow()) {
$o_instance = new $vs_table();
if ($o_instance->load($qr_res->get($vs_pk))) {
return $o_instance;
}
}
return null;
break;
case 'modelinstances':
$va_instances = array();
while ($qr_res->nextRow()) {
$o_instance = new $vs_table();
if ($o_instance->load($qr_res->get($vs_pk))) {
$va_instances[] = $o_instance;
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
}
return $va_instances;
break;
case 'firstid':
if ($qr_res->nextRow()) {
return $qr_res->get($vs_pk);
}
return null;
break;
case 'count':
return $qr_res->numRows();
break;
default:
case 'ids':
case 'searchresult':
$va_ids = array();
while ($qr_res->nextRow()) {
$va_ids[] = $qr_res->get($vs_pk);
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
if ($ps_return_as == 'searchresult') {
if (sizeof($va_ids) > 0) {
return $t_instance->makeSearchResult($t_instance->tableName(), $va_ids);
}
return null;
} else {
return $va_ids;
}
break;
}
}
/**
* @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;
}
/**
* Get sort keys for list of hits from a given table
*
* @param string $ps_element_code
* @param int $pn_table_num
* @param array $pa_hits
* @return array|bool
*/
private function _getSortKeysForElement($ps_element_code, $pn_table_num, $pa_hits)
{
if (!($t_element = ca_metadata_elements::getInstance($ps_element_code))) {
return false;
}
// is metadata element
$vn_element_id = $t_element->getPrimaryKey();
if (!($vs_sortable_value_fld = Attribute::getSortFieldForDatatype($t_element->get('datatype')))) {
return false;
}
$vs_sql = null;
switch ($vn_datatype = (int) $t_element->get('datatype')) {
case __CA_ATTRIBUTE_VALUE_LIST__:
$vs_sql = "\n\t\t\t\t\t\t\tSELECT attr.row_id, lower(lil.name_plural) name_plural\n\t\t\t\t\t\t\tFROM ca_attributes attr\n\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\tINNER JOIN ca_list_item_labels AS lil ON lil.item_id = attr_vals.item_id\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND\n\t\t\t\t\t\t\t\t(attr.table_num = ?) AND\n\t\t\t\t\t\t\t\t(lil.name_plural IS NOT NULL) AND\n\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_OBJECTS__:
case __CA_ATTRIBUTE_VALUE_ENTITIES__:
case __CA_ATTRIBUTE_VALUE_PLACES__:
case __CA_ATTRIBUTE_VALUE_OCCURRENCES__:
case __CA_ATTRIBUTE_VALUE_COLLECTIONS__:
case __CA_ATTRIBUTE_VALUE_LOANS__:
case __CA_ATTRIBUTE_VALUE_MOVEMENTS__:
case __CA_ATTRIBUTE_VALUE_STORAGELOCATIONS__:
case __CA_ATTRIBUTE_VALUE_OBJECTLOTS__:
if (!($vs_sortable_value_fld = Attribute::getSortFieldForDatatype($vn_datatype))) {
break;
}
if (!($t_auth_instance = AuthorityAttributeValue::elementTypeToInstance($vn_datatype))) {
break;
}
$vs_sql = "\n\t\t\t\t\t\t\tSELECT attr.row_id, lower(lil.{$vs_sortable_value_fld}) {$vs_sortable_value_fld}\n\t\t\t\t\t\t\tFROM ca_attributes attr\n\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\tINNER JOIN " . $t_auth_instance->getLabelTableName() . " AS lil ON lil.value_integer1 = attr_vals.item_id\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND\n\t\t\t\t\t\t\t\t(attr.table_num = ?) AND\n\t\t\t\t\t\t\t\t(lil.{$vs_sortable_value_fld} IS NOT NULL) AND\n\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_DATERANGE__:
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_sql = "\n\t\t\t\t\t\t\tSELECT attr.row_id, {$vs_sortable_value_fld}\n\t\t\t\t\t\t\tFROM ca_attributes attr\n\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\tWHERE\n\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND\n\t\t\t\t\t\t\t\t(attr.table_num = ?) AND\n\t\t\t\t\t\t\t\t(attr_vals.{$vs_sort_field} IS NOT NULL) AND\n\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t";
break;
default:
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_sql = "\n\t\t\t\t\t\t\tSELECT attr.row_id, lower({$vs_sortable_value_fld}) {$vs_sort_field}\n\t\t\t\t\t\t\tFROM ca_attributes attr\n\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\tWHERE\n\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND\n\t\t\t\t\t\t\t\t(attr.table_num = ?) AND\n\t\t\t\t\t\t\t\t(attr_vals.{$vs_sort_field} IS NOT NULL) AND\n\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t";
break;
}
if (!$vs_sql) {
return false;
}
$qr_sort = $this->opo_db->query($vs_sql, array((int) $vn_element_id, (int) $pn_table_num, $pa_hits));
$va_sort_keys = array();
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
$va_sort_keys[$va_row['row_id']] = $va_row[$vs_sort_field];
}
return $va_sort_keys;
}
/**
*
*/
public function sortHits(&$pa_hits, $ps_table, $ps_field, $ps_key, $ps_direction = 'asc', $pa_options = null)
{
//$t= new Timer();
$vs_browse_tmp_table = $this->loadListIntoTemporaryResultTable($pa_hits, $ps_key);
if (!in_array(strtolower($ps_direction), array('asc', 'desc'))) {
$ps_direction = 'asc';
}
if (!is_array($pa_hits) || !sizeof($pa_hits)) {
return $pa_hits;
}
$t_table = $this->opo_datamodel->getInstanceByTableName($ps_table, true);
$vs_table_pk = $t_table->primaryKey();
$vn_table_num = $t_table->tableNum();
$va_sort_tmp = explode('/', $ps_field);
$ps_field = $va_sort_tmp[0];
$vs_rel_type = sizeof($va_sort_tmp) > 1 ? $va_sort_tmp[1] : null;
$va_fields = explode(';', $ps_field);
$va_sorted_hits = array();
$va_joins = array();
$vs_locale_where = $vs_is_preferred_sql = '';
$vs_primary_sort_field = array_shift($va_fields);
$va_primary_sort_field = explode('.', $vs_primary_sort_field);
$va_additional_sort_fields = array();
foreach ($va_fields as $vs_additional_sort_field) {
$va_tmp = explode('.', $vs_additional_sort_field);
if ($va_tmp[0] == $va_primary_sort_field[0]) {
$va_additional_sort_fields[] = $va_tmp;
}
}
if ($va_primary_sort_field[0] == $ps_table) {
//
// sort field is in search table
//
if (!$t_table->hasField($va_primary_sort_field[1])) {
//
// is it an attribute?
//
$t_element = new ca_metadata_elements();
$vs_sort_element_code = array_pop($va_primary_sort_field);
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') == __CA_ATTRIBUTE_VALUE_LIST__) {
$vs_sortable_value_fld = 'lil.name_plural';
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$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} {$ps_direction}\n\t\t\t\t\t\t\t";
} elseif ((int) $t_element->get('datatype') == __CA_ATTRIBUTE_VALUE_DATERANGE__) {
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_sql = "\n\t\t\t\t\t\t\t\tSELECT attr.row_id, attr.locale_id, {$vs_sortable_value_fld}\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_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 (attr_vals.{$vs_sort_field} IS NOT NULL)\n\t\t\t\t\t\t\t\tORDER BY attr_vals.{$vs_sort_field} {$ps_direction}, attr.row_id\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_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_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 (attr_vals.{$vs_sort_field} IS NOT NULL)\n\t\t\t\t\t\t\t\tORDER BY attr_vals.{$vs_sort_field} {$ps_direction}, attr.row_id\n\t\t\t\t\t\t\t";
}
$qr_sort = $this->opo_db->query($vs_sql, (int) $vn_element_id, (int) $vn_table_num);
$va_sorted_hits = $qr_sort->getAllFieldValues('row_id');
// Add on hits that aren't sorted because they don't have an attribute associated
$va_missing_items = array_diff($pa_hits, $va_sorted_hits);
$va_sorted_hits = array_merge($va_sorted_hits, $va_missing_items);
}
return $va_sorted_hits;
} else {
$va_field_info = $t_table->getFieldInfo($va_primary_sort_field[1]);
if ($va_field_info['START'] && $va_field_info['END']) {
$vs_sortable_value_fld = $vs_primary_sort_field;
$va_additional_sort_fields[] = $va_field_info['END'];
} else {
$vs_sortable_value_fld = $vs_primary_sort_field;
}
}
} else {
// sort field is in related table
$va_path = $this->opo_datamodel->getPath($ps_table, $va_primary_sort_field[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);
$vs_rel_type_sql = null;
if ($t_table->isRelationship() && $vs_rel_type) {
if (is_array($va_rel_types = caMakeRelationshipTypeIDList($vs_table, array($vs_rel_type))) && sizeof($va_rel_types)) {
$vs_rel_type_sql = " AND {$vs_table}.type_id IN (" . join(",", $va_rel_types) . ")";
}
}
if ($vs_last_table) {
$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'] . $vs_rel_type_sql;
} 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'] . $vs_rel_type_sql;
}
}
$t_last_table = $t_table;
$vs_last_table = $vs_table;
}
$vs_sortable_value_fld = $vs_primary_sort_field;
} else {
$va_rels = $this->opo_datamodel->getRelationships($ps_table, $va_primary_sort_field[0]);
if (!$va_rels) {
return $pa_hits;
}
// return hits unsorted if field is not valid
$t_rel = $this->opo_datamodel->getInstanceByTableName($va_primary_sort_field[0], true);
if (!$t_rel->hasField($va_primary_sort_field[1])) {
return $pa_hits;
}
$va_joins[$va_primary_sort_field[0]] = 'LEFT JOIN ' . $va_primary_sort_field[0] . ' ON ' . $ps_table . '.' . $va_rels[$ps_table][$va_primary_sort_field[0]][0][0] . ' = ' . $va_primary_sort_field[0] . '.' . $va_rels[$ps_table][$va_primary_sort_field[0]][0][1] . "\n";
// 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_primary_sort_field[0] . ".is_preferred = 1";
}
$vs_sortable_value_fld = $vs_primary_sort_field;
}
}
//
// Grab values and index for sorting later
//
//Debug::msg("sort pre query ".$t->getTime(4));
$va_primary_sort_field = explode('.', $vs_sortable_value_fld);
$vs_join_sql = join("\n", $va_joins);
$va_sort_fields = array("{$vs_sortable_value_fld} {$ps_direction}");
foreach ($va_additional_sort_fields as $va_additional_sort_field) {
$va_sort_fields[] = "{$va_additional_sort_field[0]}.{$va_additional_sort_field[1]} {$ps_direction}";
}
$vs_sql = "\n\t\t\t\tSELECT {$ps_table}.{$vs_table_pk}\n\t\t\t\tFROM {$ps_table}\n\t\t\t\t{$vs_join_sql}\n\t\t\t\tINNER JOIN {$vs_browse_tmp_table} ON {$vs_browse_tmp_table}.row_id = {$ps_table}.{$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\tORDER BY " . join(',', $va_sort_fields) . ", {$ps_table}.{$vs_table_pk}\n\t\t\t";
$qr_sort = $this->opo_db->query($vs_sql);
$va_sorted_hits = $qr_sort->getAllFieldValues($vs_table_pk);
return $va_sorted_hits;
}
/**
*
* @param array $pa_hits
* @param string $ps_table The table being sorted
* @param string $ps_field A semicolon-delimited string of fully qualified bundle names (Eg. ca_objects.idno;ca_objects.due_date)
* @param string $ps_key Key to use for temporary storage
* @param string $ps_direction Direction to sort
* @param array $pa_options
*
* @return array
*/
public function sortHits(&$pa_hits, $ps_table, $ps_field, $ps_direction = 'asc', $pa_options = null)
{
if (!($t_table = $this->opo_datamodel->getInstanceByTableName($ps_table, true))) {
return null;
}
// invalid table
$vs_table_pk = $t_table->primaryKey();
$vn_table_num = $t_table->tableNum();
// TODO: allow override of this with field-specific directions
// Default direction
if (!in_array(strtolower($ps_direction), array('asc', 'desc'))) {
$ps_direction = 'asc';
}
// Don't try to sort empty results
if (!is_array($pa_hits) || !sizeof($pa_hits)) {
return $pa_hits;
}
// Get field list
//$va_sort_tmp = explode('/', $ps_field); // strip any relationship type
//$ps_field = $va_sort_tmp[0];
//$vs_rel_type = (sizeof($va_sort_tmp) > 1) ? $va_sort_tmp[1] : null;
$va_bundles = explode(';', $ps_field);
// $va_sort_tmp[0]);
$va_sorted_hits = array();
$vs_sort_tmp_table = null;
$va_sort_key_values = array();
foreach ($va_bundles as $vs_bundle) {
$va_sort_tmp = explode('/', $vs_bundle);
// strip any relationship type
$vs_rel_type = sizeof($va_sort_tmp) > 1 ? $va_sort_tmp[1] : null;
$vs_bundle = $va_sort_tmp[0];
list($vs_field_table, $vs_field, $vs_subfield) = explode(".", $vs_bundle);
if (!($t_instance = $this->opo_datamodel->getInstanceByTableName($vs_field_table, true))) {
break;
}
// Transform preferred_labels
if ($vs_field == 'preferred_labels') {
$vs_field_table = $t_instance->getLabelTableName();
$vs_field = $vs_subfield ? $vs_subfield : $t_instance->getLabelDisplayField();
$vs_subfield = null;
}
if ($vs_field_table === $ps_table) {
// sort in primary table
if (!$t_table->hasField($vs_field)) {
if ($t_element = ca_metadata_elements::getInstance($vs_subfield ? $vs_subfield : $vs_field)) {
// is metadata element
$vn_element_id = $t_element->getPrimaryKey();
if (!($vs_sortable_value_fld = Attribute::getSortFieldForDatatype($t_element->get('datatype')))) {
break;
}
switch ($vn_datatype = (int) $t_element->get('datatype')) {
case __CA_ATTRIBUTE_VALUE_LIST__:
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT attr.row_id, lower(lil.name_plural) name_plural\n\t\t\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\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\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\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr.table_num = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(lil.name_plural IS NOT NULL) AND\n\t\t\t\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_OBJECTS__:
case __CA_ATTRIBUTE_VALUE_ENTITIES__:
case __CA_ATTRIBUTE_VALUE_PLACES__:
case __CA_ATTRIBUTE_VALUE_OCCURRENCES__:
case __CA_ATTRIBUTE_VALUE_COLLECTIONS__:
case __CA_ATTRIBUTE_VALUE_LOANS__:
case __CA_ATTRIBUTE_VALUE_MOVEMENTS__:
case __CA_ATTRIBUTE_VALUE_STORAGELOCATIONS__:
case __CA_ATTRIBUTE_VALUE_OBJECTLOTS__:
if (!($vs_sortable_value_fld = Attribute::getSortFieldForDatatype($vn_datatype))) {
break;
}
if (!($t_auth_instance = AuthorityAttributeValue::elementTypeToInstance($vn_datatype))) {
break;
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT attr.row_id, lower(lil.{$vs_sortable_value_fld}) {$vs_sortable_value_fld}\n\t\t\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\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\t\t\tINNER JOIN " . $t_auth_instance->getLabelTableName() . " AS lil ON lil.value_integer1 = attr_vals.item_id\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr.table_num = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(lil.{$vs_sortable_value_fld} IS NOT NULL) AND\n\t\t\t\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_DATERANGE__:
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT attr.row_id, {$vs_sortable_value_fld}\n\t\t\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\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\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr.table_num = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.{$vs_sort_field} IS NOT NULL) AND\n\t\t\t\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t\t\t\t";
break;
default:
$vs_sortable_value_fld = 'attr_vals.' . $vs_sortable_value_fld;
$vs_sort_field = array_pop(explode('.', $vs_sortable_value_fld));
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT attr.row_id, lower({$vs_sortable_value_fld}) {$vs_sort_field}\n\t\t\t\t\t\t\t\t\t\tFROM ca_attributes attr\n\t\t\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\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.element_id = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr.table_num = ?) AND \n\t\t\t\t\t\t\t\t\t\t\t(attr_vals.{$vs_sort_field} IS NOT NULL) AND\n\t\t\t\t\t\t\t\t\t\t\t(attr.row_id IN (?))\n\t\t\t\t\t\t\t\t\t";
break;
}
$qr_sort = $this->opo_db->query($vs_sql, array((int) $vn_element_id, (int) $vn_table_num, $pa_hits));
$va_sort_keys = array();
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
$va_sort_keys[$va_row['row_id']] = $va_row[$vs_sort_field];
}
$va_sort_key_values[] = $va_sort_keys;
}
} else {
// is intrinsic
$va_field_info = $t_table->getFieldInfo($vs_field);
if ($va_field_info['START'] && $va_field_info['END']) {
$vs_field = $va_field_info['START'];
}
$vs_sql = "\n\t\t\t\t\t\t\tSELECT {$vs_table_pk}, {$vs_field}\n\t\t\t\t\t\t\tFROM {$ps_table}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_table_pk} IN (?)\n\t\t\t\t\t\t";
$qr_sort = $this->opo_db->query($vs_sql, array($pa_hits));
$va_sort_keys = array();
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
$va_sort_keys[$va_row[$vs_table_pk]] = $va_row[$vs_field];
}
$va_sort_key_values[] = $va_sort_keys;
}
} else {
// sort in related table
if ($vs_field_table == 'ca_set_items' && $vs_field == 'rank' && (int) $vs_rel_type > 0) {
// sort by ranks in specific set
$vs_sql = "\n\t\t\t\t\t\t\tSELECT {$ps_table}.{$vs_table_pk}, ca_set_items.rank\n\t\t\t\t\t\t\tFROM ca_sets\n\t\t\t\t\t\t\tINNER JOIN ca_set_items ON ca_set_items.set_id = ca_sets.set_id\n\t\t\t\t\t\t\tINNER JOIN {$ps_table} ON {$ps_table}.{$vs_table_pk} = ca_set_items.row_id\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(ca_set_items.table_num = ?) AND\n\t\t\t\t\t\t\t\t(ca_set_items.set_id = ?) AND\n\t\t\t\t\t\t\t\t{$ps_table}.{$vs_table_pk} IN (?)\n\t\t\t\t\t\t";
$qr_sort = $this->opo_db->query($vs_sql, array($vn_table_num, (int) $vs_rel_type, $pa_hits));
} else {
$t_rel = $this->opo_datamodel->getInstanceByTableName($vs_field_table, true);
if (!$t_rel->hasField($vs_field)) {
break;
}
$va_path = $this->opo_datamodel->getPath($ps_table, $vs_field_table);
$vs_is_preferred_sql = null;
$va_joins = array();
if (sizeof($va_path) > 2) {
// many-many
$vs_last_table = null;
// generate related joins
foreach ($va_path as $vs_table => $va_info) {
$t_instance = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
$vs_rel_type_sql = null;
if ($t_instance->isRelationship() && $vs_rel_type) {
if (is_array($va_rel_types = caMakeRelationshipTypeIDList($vs_table, array($vs_rel_type))) && sizeof($va_rel_types)) {
$vs_rel_type_sql = " AND {$vs_table}.type_id IN (" . join(",", $va_rel_types) . ")";
}
}
if ($vs_last_table) {
$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'] . $vs_rel_type_sql;
} 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'] . $vs_rel_type_sql;
}
}
$vs_last_table = $vs_table;
}
} else {
$va_rels = $this->opo_datamodel->getRelationships($ps_table, $vs_field_table);
if (!$va_rels) {
break;
}
// field is not valid
// TODO: allow sorting on related record attributes
$va_joins[$vs_field_table] = 'INNER JOIN ' . $vs_field_table . ' ON ' . $ps_table . '.' . $va_rels[$ps_table][$vs_field_table][0][0] . ' = ' . $vs_field_table . '.' . $va_rels[$ps_table][$vs_field_table][0][1] . "\n";
// 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 = " {$vs_field_table}.is_preferred = 1";
}
}
$vs_join_sql = join("\n", $va_joins);
$vs_sql = "\n\t\t\t\t\t\t\tSELECT {$ps_table}.{$vs_table_pk}, {$vs_field_table}.{$vs_field}\n\t\t\t\t\t\t\tFROM {$ps_table}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_is_preferred_sql} " . ($vs_is_preferred_sql ? ' AND ' : '') . " {$ps_table}.{$vs_table_pk} IN (?)\n\t\t\t\t\t\t";
$qr_sort = $this->opo_db->query($vs_sql, array($pa_hits));
}
$va_sort_keys = array();
while ($qr_sort->nextRow()) {
$va_row = $qr_sort->getRow();
$va_sort_keys[$va_row[$vs_table_pk]] = $va_row[$vs_field];
}
$va_sort_key_values[] = $va_sort_keys;
}
}
return $this->_doSort($pa_hits, $va_sort_key_values, $ps_direction);
}
