/** * Helper method to compose SQL where statements for fulltext searching */ private function fulltext_sql_where($value, $mode, $col = 'words', $bool = 'AND') { $WS = ' '; $AS = $col == 'words' ? $WS : self::SEPARATOR; $words = $col == 'words' ? rcube_utils::normalize_string($value, true) : array($value); $where = array(); foreach ($words as $word) { if ($mode & rcube_addressbook::SEARCH_STRICT) { $where[] = '(' . $this->db->ilike($col, $word) . ' OR ' . $this->db->ilike($col, $word . $AS . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $word . $AS . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $word) . ')'; } else { if ($mode & rcube_addressbook::SEARCH_PREFIX) { $where[] = '(' . $this->db->ilike($col, $word . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $word . '%') . ')'; } else { $where[] = $this->db->ilike($col, '%' . $word . '%'); } } } return count($where) ? '(' . join(" {$bool} ", $where) . ')' : ''; }
/** * Helper method to compose SQL where statements for fulltext searching */ private function fulltext_sql_where($value, $mode, $col = 'words', $bool = 'AND') { $WS = ' '; $AS = $col == 'words' ? $WS : self::SEPARATOR; $words = $col == 'words' ? rcube_utils::normalize_string($value, true) : array($value); $where = array(); foreach ($words as $word) { switch ($mode) { case 1: // strict $where[] = '(' . $this->db->ilike($col, $word . '%') . ' OR ' . $this->db->ilike($col, '%' . $WS . $word . $WS . '%') . ' OR ' . $this->db->ilike($col, '%' . $WS . $word) . ')'; break; case 2: // prefix $where[] = '(' . $this->db->ilike($col, $word . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $word . '%') . ')'; break; default: // partial $where[] = $this->db->ilike($col, '%' . $word . '%'); } } return count($where) ? '(' . join(" {$bool} ", $where) . ')' : ''; }
/** * Search contacts * * @param mixed $fields The field name of array of field names to search in * @param mixed $value Search value (or array of values when $fields is array) * @param int $mode Matching mode: * 0 - partial (*abc*), * 1 - strict (=), * 2 - prefix (abc*) * @param boolean $select True if results are requested, False if count only * @param boolean $nocount True to skip the count query (select only) * @param array $required List of fields that cannot be empty * * @return object rcube_result_set Contact records and 'count' value */ function search($fields, $value, $mode = 0, $select = true, $nocount = false, $required = array()) { if (!is_array($fields)) { $fields = array($fields); } if (!is_array($required) && !empty($required)) { $required = array($required); } $where = $and_where = array(); $mode = intval($mode); $WS = ' '; $AS = self::SEPARATOR; foreach ($fields as $idx => $col) { // direct ID search if ($col == 'ID' || $col == $this->primary_key) { $ids = !is_array($value) ? explode(self::SEPARATOR, $value) : $value; $ids = $this->db->array2list($ids, 'integer'); $where[] = 'c.' . $this->primary_key . ' IN (' . $ids . ')'; continue; } else { if ($col == '*') { $words = array(); foreach (explode($WS, rcube_utils::normalize_string($value)) as $word) { switch ($mode) { case 1: // strict $words[] = '(' . $this->db->ilike('words', $word . '%') . ' OR ' . $this->db->ilike('words', '%' . $WS . $word . $WS . '%') . ' OR ' . $this->db->ilike('words', '%' . $WS . $word) . ')'; break; case 2: // prefix $words[] = '(' . $this->db->ilike('words', $word . '%') . ' OR ' . $this->db->ilike('words', '%' . $WS . $word . '%') . ')'; break; default: // partial $words[] = $this->db->ilike('words', '%' . $word . '%'); } } $where[] = '(' . join(' AND ', $words) . ')'; } else { $val = is_array($value) ? $value[$idx] : $value; // table column if (in_array($col, $this->table_cols)) { switch ($mode) { case 1: // strict $where[] = '(' . $this->db->quoteIdentifier($col) . ' = ' . $this->db->quote($val) . ' OR ' . $this->db->ilike($col, $val . $AS . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $val . $AS . '%') . ' OR ' . $this->db->ilike($col, '%' . $AS . $val) . ')'; break; case 2: // prefix $where[] = '(' . $this->db->ilike($col, $val . '%') . ' OR ' . $this->db->ilike($col, $AS . $val . '%') . ')'; break; default: // partial $where[] = $this->db->ilike($col, '%' . $val . '%'); } } else { if (in_array($col, $this->fulltext_cols)) { foreach (rcube_utils::normalize_string($val, true) as $word) { switch ($mode) { case 1: // strict $words[] = '(' . $this->db->ilike('words', $word . $WS . '%') . ' OR ' . $this->db->ilike('words', '%' . $AS . $word . $WS . '%') . ' OR ' . $this->db->ilike('words', '%' . $AS . $word) . ')'; break; case 2: // prefix $words[] = '(' . $this->db->ilike('words', $word . '%') . ' OR ' . $this->db->ilike('words', $AS . $word . '%') . ')'; break; default: // partial $words[] = $this->db->ilike('words', '%' . $word . '%'); } } $where[] = '(' . join(' AND ', $words) . ')'; } if (is_array($value)) { $post_search[$col] = mb_strtolower($val); } } } } } foreach (array_intersect($required, $this->table_cols) as $col) { $and_where[] = $this->db->quoteIdentifier($col) . ' <> ' . $this->db->quote(''); } if (!empty($where)) { // use AND operator for advanced searches $where = join(is_array($value) ? ' AND ' : ' OR ', $where); } if (!empty($and_where)) { $where = ($where ? "({$where}) AND " : '') . join(' AND ', $and_where); } // Post-searching in vCard data fields // we will search in all records and then build a where clause for their IDs if (!empty($post_search)) { $ids = array(0); // build key name regexp $regexp = '/^(' . implode(array_keys($post_search), '|') . ')(?:.*)$/'; // use initial WHERE clause, to limit records number if possible if (!empty($where)) { $this->set_search_set($where); } // count result pages $cnt = $this->count(); $pages = ceil($cnt / $this->page_size); $scnt = count($post_search); // get (paged) result for ($i = 0; $i < $pages; $i++) { $this->list_records(null, $i, true); while ($row = $this->result->next()) { $id = $row[$this->primary_key]; $found = array(); foreach (preg_grep($regexp, array_keys($row)) as $col) { $pos = strpos($col, ':'); $colname = $pos ? substr($col, 0, $pos) : $col; $search = $post_search[$colname]; foreach ((array) $row[$col] as $value) { // composite field, e.g. address foreach ((array) $value as $val) { $val = mb_strtolower($val); switch ($mode) { case 1: $got = $val == $search; break; case 2: $got = $search == substr($val, 0, strlen($search)); break; default: $got = strpos($val, $search) !== false; break; } if ($got) { $found[$colname] = true; break 2; } } } } // all fields match if (count($found) >= $scnt) { $ids[] = $id; } } } // build WHERE clause $ids = $this->db->array2list($ids, 'integer'); $where = 'c.' . $this->primary_key . ' IN (' . $ids . ')'; // reset counter unset($this->cache['count']); // when we know we have an empty result if ($ids == '0') { $this->set_search_set($where); return $this->result = new rcube_result_set(0, 0); } } if (!empty($where)) { $this->set_search_set($where); if ($select) { $this->list_records(null, 0, $nocount); } else { $this->result = $this->count(); } } return $this->result; }