/** * 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); }