Perform a wildcard query by using Tablestore SDK for PHP - Tablestore

When you perform a wildcard query, you can use the asterisk (*) and question mark (?) wildcard characters in the query to search for data. The asterisk (*) matches a string of any length at, before, or after a search term. The question mark (?) matches a single character in a specific position. The string can start with an asterisk (*) or a question mark (?). For example, if you search for the "table*e" string, "tablestore" can be matched.

If you want to use *word* (equivalent to WHERE field_a LIKE '%word%' in SQL) to query data, you can use fuzzy query for better performance. For more information, see Fuzzy query. If you perform a fuzzy query, the query performance is not compromised when the data volume increases.

Note

If you want to use the NOT LIKE operator, you must use WildcardQuery together with must_not_queries of BoolQuery.

Prerequisites

An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
A data table is created. Data is written to the table. For more information, see Create a data table and Write data.
A search index is created for the data table. For more information, see Create a search index.

Parameters

Parameter	Description
table_name	The name of the data table.
index_name	The name of the search index.
offset	The position from which the current query starts.
limit	The maximum number of rows that you want the current query to return.
get_total_count	Specifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which indicates that the total number of rows that meet the query conditions is not returned. If you set the get_total_count parameter to true, the query performance is compromised.
query_type	The query type. Set the query type to `QueryTypeConst::WILDCARD_QUERY`.
field_name	The name of the column that you want to query.
value	The string that contains wildcard characters. The string cannot exceed 32 characters in length.
sort	The method that you want to use to sort the rows in the response. For more information, see Sorting and paging.
columns_to_get	Specifies whether to return all columns of each row that meets the query conditions. You can configure return_type and return_names for this parameter. If you set return_type to `ColumnReturnTypeConst::RETURN_SPECIFIED`, you can use return_names to specify the columns to return. If you set the return_type parameter to `ColumnReturnTypeConst::RETURN_ALL`, all columns are returned. If you set return_type to `ColumnReturnTypeConst::RETURN_ALL_FROM_INDEX`, all columns in the search index are returned. If you set the return_type parameter to `ColumnReturnTypeConst::RETURN_NONE`, only the primary key columns are returned.

Examples

The following sample code shows how to query data that matches the key* condition in the keyword column of a table.

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_search_index',
    'search_query' => array(
        'offset' => 0,
        'limit' => 2,
        'get_total_count' => true,
        'query' => array(
            'query_type' => QueryTypeConst::WILDCARD_QUERY,
            'query' => array(
                'field_name' => 'keyword',
                'value' => 'key*'
            )
        ),
        'sort' => array(
            array(
                'field_sort' => array(
                    'field_name' => 'keyword',
                    'order' => SortOrderConst::SORT_ORDER_ASC
                )
            ),
        )
    ),
    'columns_to_get' => array(
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
        'return_names' => array('keyword')
    )
);
$response = $otsClient->search($request);

FAQ

References

When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, Boolean query, geo query, nested query, and exists query. You can use different query methods to query data from multiple dimensions based on your business requirements.
If you want to sort or paginate the rows that meet the query conditions, you can use the sorting and paging feature. For more information, see Sorting and paging.
If you want to collapse the result set based on a specific column, you can use the collapse (distinct) feature. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).
If you want to analyze data in a data table, such as obtaining the extreme values, sum, and total number of rows, you can perform aggregation operations or execute SQL statements. For more information, see Aggregation and SQL query.
If you want to quickly obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.