`yf_search_driver_db_like` class

Search in a database table with one LIKE per word (pgsql: ILIKE): every word must be in one of the fields. index = table (DB_PREFIX added like db()->from()), fields = columns. The table itself is the source, so add / update / delete / bulk and the index DDL are not supported. Values are escaped here with db()->es() and (int) / (float): the query builder does not escape, so SQL is built as text and run through get_all().

File	`plugins/search/classes/search/yf_search_driver_db_like.class.php`
Use	`_class('search_driver_db_like', 'classes/search/')`
Extends	`yf_search_driver`

Public properties

A site sets them in its config with $PROJECT_CONF['search_driver_db_like']['NAME'] = ...;

Property	Default	Description
`$DB` object\|null	`db() */ Property [ public $DB = NULL`	yf_db instance to search in, null = db()
`$PARENT`	`null`
`$SUGGEST_LIMIT` int	`10`	Default number of suggest() results

Inherited properties

from yf_search_driver: $DEF_LIMIT, $MAX_LIMIT, $MAX_WINDOW, $MAX_WORDS, $last_error

Public methods 22

capabilities

public function capabilities()

    public function capabilities()
    {
        return ['highlight' => true, 'suggest' => true, 'facets' => true] + parent::capabilities();
    }

connect

public function connect()

    public function connect()
    {
        $db = $this->_db();
        if ( ! is_object($db)) {
            return $this->_fail('no database');
        }
        if (empty($db->_connected) && ! $db->connect()) {
            return $this->_fail('cannot connect to the database');
        }
        return $db;
    }

escape

public function escape($text)

Text for '...%' patterns: LIKE wildcards and the backslash escaped first, then the literal escaping.

    public function escape($text)
    {
        return $this->_like_escape($text);
    }

is_ready

public function is_ready()

    public function is_ready()
    {
        $db = $this->_db();
        return is_object($db) && ! empty($db->_connected);
    }

raw

public function raw($query)

SQL passthrough to get_all(); nothing is escaped.

    public function raw($query)
    {
        if ( ! $this->connect()) {
            return false;
        }
        $error = '';
        $rows = $this->_get_all((string) $query, $error);
        return $rows === false ? $this->_fail($error) : $rows;
    }

search

public function search(array $params = [], &$error_message = '')

$params array

    public function search(array $params = [], &$error_message = '')
    {
        $started = microtime(true);
        $error_message = $error = '';
        $p = $this->_normalize_params($params, $error);
        if ($p === false) {
            return $this->_fail($error, $error_message);
        }
        $db = $this->connect();
        if ( ! $db) {
            $error_message = $this->last_error;
            return false;
        }
        $words = $this->_words($p['q']);
        if ($words && ! $p['fields']) {
            return $this->_fail('q needs fields', $error_message);
        }
        [$text_where, $score] = $words ? $this->_text_sql($p, $words) : ['', '0'];
        $where = $this->_filters_sql($p['filters']);
        $text_where !== '' && array_unshift($where, $text_where);
        $where_sql = $where ? ' WHERE ' . implode(' AND ', $where) : '';
        $table = $this->_table($p['index']);
        $id = $this->_quote_name($p['id_field']);
        $columns = '*';
        if ($p['return']) {
            $columns = implode(', ', array_map([$this, '_quote_name'], array_unique([$p['id_field'], ...$p['return']])));
        }
        $order = [];
        $sort = $p['sort'] ?: ($words ? ['_score' => 'desc'] : []);
        foreach ($sort as $name => $dir) {
            $order[] = ($name === '_score' ? '_score' : $this->_quote_name($name)) . ' ' . strtoupper($dir);
        }
        if ( ! isset($sort[$p['id_field']])) {
            $order[] = $id . ' ASC';
        }
        $sql = 'SELECT ' . $columns . ', ' . $score . ' AS _score FROM ' . $table . $where_sql
            . ' ORDER BY ' . implode(', ', $order) . ' LIMIT ' . (int) $p['limit'] . ' OFFSET ' . (int) $p['offset'];
        $rows = $this->_get_all($sql, $error);
        if ($rows === false) {
            return $this->_fail($error, $error_message);
        }
        if ($p['offset'] === 0 && count($rows) < $p['limit']) {
            $total = count($rows);
        } else {
            $count = $this->_get_all('SELECT COUNT(*) AS _count FROM ' . $table . $where_sql, $error);
            if ($count === false) {
                return $this->_fail($error, $error_message);
            }
            $total = (int) ($count[0]['_count'] ?? 0);
        }
        $hl_words = $p['highlight'] ? $this->_highlight_words($words) : [];
        $items = [];
        foreach ($rows as $row) {
            $item = ['id' => $row[$p['id_field']] ?? null, 'score' => (float) ($row['_score'] ?? 0)];
            unset($row['_score']);
            $item['doc'] = $row;
            $item['highlight'] = [];
            foreach ($p['highlight']['fields'] ?? [] as $name) {
                if (isset($row[$name])) {
                    $item['highlight'][$name] = $this->_highlight_text((string) $row[$name], $hl_words, $p['highlight']['pre'], $p['highlight']['post']);
                }
            }
            $items[] = $item;
        }
        $facets = [];
        foreach ($p['facets'] as $name) {
            $col = $this->_quote_name($name);
            $sql = 'SELECT ' . $col . ' AS _value, COUNT(*) AS _count FROM ' . $table . $where_sql
                . ' GROUP BY ' . $col . ' ORDER BY _count DESC, _value ASC LIMIT ' . (int) $p['facet_limit'];
            $facet_rows = $this->_get_all($sql, $error);
            if ($facet_rows === false) {
                return $this->_fail($error, $error_message);
            }
            $facets[$name] = [];
            foreach ($facet_rows as $row) {
                $facets[$name][] = ['value' => $row['_value'], 'count' => (int) $row['_count']];
            }
        }
        $this->last_error = '';
        return [
            'total' => $total,
            'total_relation' => 'eq',
            'items' => $items,
            'facets' => $facets,
            'took_ms' => round((microtime(true) - $started) * 1000, 2),
        ];
    }

suggest

public function suggest($text, $params = [])

Distinct values of params['field'] that start with $text.

$params mixed index, field, limit

    public function suggest($text, $params = [])
    {
        $params = (array) $params;
        $field = $params['field'] ?? '';
        if ( ! $this->_is_valid_name($params['index'] ?? '', true) || ! $this->_is_valid_name($field)) {
            return $this->_fail('suggest needs index and field');
        }
        $text = trim((string) $text);
        if ($text === '') {
            return [];
        }
        if ( ! $this->connect()) {
            return false;
        }
        $col = $this->_quote_name($field);
        $limit = min(max(1, (int) $this->MAX_LIMIT), max(1, (int) ($params['limit'] ?? $this->SUGGEST_LIMIT)));
        $sql = 'SELECT DISTINCT ' . $col . ' AS _value FROM ' . $this->_table($params['index'])
            . ' WHERE ' . $col . ' ' . $this->_like_op() . " '" . $this->_like_escape($text) . "%'"
            . ' ORDER BY _value ASC LIMIT ' . $limit;
        $error = '';
        $rows = $this->_get_all($sql, $error);
        if ($rows === false) {
            return $this->_fail($error);
        }
        return array_map('strval', array_column($rows, '_value'));
    }

Methods starting with `_` hooks and helpers; not routable as page actions

__call

public function __call($name, $args)

Catch missing method call.

    public function __call($name, $args)
    {
        return main()->extend_call($this, $name, $args);
    }

_db

public function _db()

    public function _db()
    {
        return is_object($this->DB) ? $this->DB : db();
    }

_family

public function _family()

    public function _family()
    {
        $db = $this->_db();
        return is_object($db) && method_exists($db, 'get_driver_family') ? $db->get_driver_family() : 'mysql';
    }

_filters_sql

public function _filters_sql($filters)

$filters mixed normalized filters

Returns string[]

    public function _filters_sql($filters)
    {
        $sql = [];
        foreach ((array) $filters as $name => $conds) {
            $col = $this->_quote_name($name);
            foreach ($conds as [$op, $value]) {
                if ($op === 'in' || $op === 'not_in') {
                    $sql[] = $col . ($op === 'in' ? ' IN (' : ' NOT IN (') . implode(', ', array_map([$this, '_value'], $value)) . ')';
                } else {
                    $sql[] = $col . ' ' . $op . ' ' . $this->_value($value);
                }
            }
        }
        return $sql;
    }

_get_all

public function _get_all($sql, &$error = '')

Rows as a list, [] for none, false on a query error (the reason in $error).

    public function _get_all($sql, &$error = '')
    {
        $db = $this->_db();
        $rows = $db->get_all($sql, -1, false);
        $db_error = $db->last_error();
        if ($db_error) {
            $error = 'query failed: ' . ($db_error['message'] ?? 'unknown error');
            return false;
        }
        return array_values((array) $rows);
    }

_highlight_text

public function _highlight_text($text, $words, $pre = '<b>', $post = '</b>')

HTML of $text with every word wrapped in $pre / $post; the text is escaped piece by piece, so a word never matches inside an entity. $pre / $post are developer HTML, not escaped.

    public function _highlight_text($text, $words, $pre = '<b>', $post = '</b>')
    {
        $words = array_values(array_filter(array_map('strval', (array) $words), 'strlen'));
        if ( ! $words) {
            return _prepare_html($text, 0);
        }
        usort($words, fn ($a, $b) => strlen($b) <=> strlen($a));
        $re = '~(' . implode('|', array_map(fn ($w) => preg_quote($w, '~'), $words)) . ')~iu';
        $parts = preg_split($re, $text, -1, PREG_SPLIT_DELIM_CAPTURE);
        if ($parts === false) {
            return _prepare_html($text, 0);
        }
        $out = '';
        foreach ($parts as $i => $part) {
            $html = _prepare_html($part, 0);
            $out .= $i % 2 ? $pre . $html . $post : $html;
        }
        return $out;
    }

_highlight_words

public function _highlight_words($words)

Words to mark in highlight.

Returns string[]

    public function _highlight_words($words)
    {
        return (array) $words;
    }

_init

public function _init()

Kept empty for site subclasses that call parent::_init(); it no longer loads send_mail.

    public function _init()
    {
    }

_like_escape

public function _like_escape($text)

Not escape(): db_fulltext overrides that one and falls back to these LIKE patterns.

    public function _like_escape($text)
    {
        return (string) $this->_db()->es(addcslashes((string) $text, '\\%_'));
    }

_like_op

public function _like_op()

    public function _like_op()
    {
        return $this->_family() === 'pgsql' ? 'ILIKE' : 'LIKE';
    }

_number

public function _number($number)

$number mixed int or float

    public function _number($number)
    {
        if (is_int($number)) {
            return (string) $number;
        }
        return is_finite((float) $number) ? rtrim(rtrim(sprintf('%.10F', (float) $number), '0'), '.') : '0';
    }

_quote_name

public function _quote_name($name)

Quoted identifier; names were validated by _normalize_params(), quoting is the second line of defence.

    public function _quote_name($name)
    {
        $q = $this->_family() === 'pgsql' ? '"' : '`';
        return $q . str_replace($q, $q . $q, (string) $name) . $q;
    }

_table

public function _table($index)

Quoted table name with DB_PREFIX, "db.table" kept.

    public function _table($index)
    {
        $db = $this->_db();
        $name = method_exists($db, '_fix_table_name') ? (string) $db->_fix_table_name((string) $index) : (string) $index;
        return implode('.', array_map([$this, '_quote_name'], explode('.', $name)));
    }

_text_sql

public function _text_sql($p, $words)

WHERE part and score expression for the words of q.

$p mixed normalized params

Returns array{0: string, 1: string}

    public function _text_sql($p, $words)
    {
        $op = $this->_like_op();
        $where = [];
        $score = [];
        foreach ($words as $word) {
            $pattern = "'%" . $this->_like_escape($word) . "%'";
            $any = [];
            foreach ($p['fields'] as $name => $weight) {
                $cond = $this->_quote_name($name) . ' ' . $op . ' ' . $pattern;
                $any[] = $cond;
                $score[] = 'CASE WHEN ' . $cond . ' THEN ' . $this->_number($weight) . ' ELSE 0 END';
            }
            $where[] = '(' . implode(' OR ', $any) . ')';
        }
        return [implode(' AND ', $where), '(' . implode(' + ', $score) . ')'];
    }

_value

public function _value($value)

SQL literal of a filter value: ints and finite floats unquoted, the rest escaped and quoted.

    public function _value($value)
    {
        if (is_int($value) || is_bool($value)) {
            return (string) (int) $value;
        }
        if (is_float($value) && is_finite($value)) {
            return $this->_number($value);
        }
        return "'" . $this->_db()->es((string) $value) . "'";
    }

Inherited methods

from yf_search_driver: _doc_id(), _fail(), _highlight_html(), _highlight_markers(), _is_valid_name(), _name(), _normalize_filter(), _normalize_params(), _not_supported(), _without_id(), _words(), add(), bulk(), create_index(), delete(), drop_index(), index_exists(), truncate(), update()

yf_search_driver_db_like class