Tablestore PHP SDK filters - Tablestore - Alibaba Cloud Documentation Center

Tablestore supports server-side data filtering during queries. This topic describes how to use filters in the Tablestore PHP SDK.

Prerequisites

Initialize a Tablestore client

Filter types

Tablestore supports two filter types.

SingleColumnValueFilter: checks whether the value of a single attribute column meets a condition.
CompositeColumnValueFilter: combines multiple conditions for data filtering.

SingleColumnValueFilter

[
    'column_name' => '<string>',
    'value' => <ColumnValue>,
    'comparator' => <ComparatorType>,
    'pass_if_missing' => true || false,
    'latest_version_only' => true || false
]

Parameters

Name	Type	Description
comparator (required)	ComparatorTypeConst	A relational operator. Valid values: CONST_EQUAL (=), CONST_NOT_EQUAL (!=), CONST_GREATER_THAN (>), CONST_GREATER_EQUAL (>=), CONST_LESS_THAN (<), and CONST_LESS_EQUAL (<=).
column_name (required)	string	The name of the attribute column to check.
value (required)	STRING, INTEGER, BINARY, DOUBLE, or BOOLEAN	The value to evaluate.
pass_if_missing (optional)	bool	Specifies whether to return a row if the attribute column does not exist in that row. Default is true, which means the row is returned.
latest_version_only (optional)	bool	Specifies whether to use only the latest version of data in the attribute column for comparison. Default is true, which means only the latest version is checked against the filter condition.

Example

This example uses a range query to read rows with primary keys in the range [row1, row3) and then filters the results to return only rows where the col1 attribute column equals val1.

$request = array (
    'table_name' => 'test_table',
    // Set the start primary key for the range query.
    'inclusive_start_primary_key' => array (
        array('id', 'row1')
    ),
    // Set the end primary key for the range query. The result does not include this key.
    'exclusive_end_primary_key'  => array (
        array('id', 'row3')
    ),
    // Read data in forward order.
    'direction' => DirectionConst::CONST_FORWARD,
    // Read the latest version of data.
    'max_versions' => 1,
    // Build a filter to return rows where col1 equals "val1".
    'column_filter' => array (
        'column_name' => 'col1',
        'value' => 'val1',
        'comparator' => ComparatorTypeConst::CONST_EQUAL
    )
);

try {
    // Call getRange to read rows.
    $response = $client->getRange ($request);

    // Process the response.
    echo "* Read CU Cost: " . $response['consumed']['capacity_unit']['read'] . "\n";
    echo "* Write CU Cost: " . $response['consumed']['capacity_unit']['write'] . "\n";
    echo "* Row Data: " . "\n";
    foreach ($response['rows'] as $row) {
        echo json_encode($row) . "\n";
    }
} catch (Exception $e){
    echo "Get Range failed.";
}

Do not return a row if the attribute column does not exist in that row.
```
$request['column_filter']['pass_if_missing'] = false;
```
Check all versions of data in the attribute column. Return the row if any version meets the condition.
```
$request['column_filter']['latest_version_only'] = false;
```

Combined filters

You can combine up to 32 conditions.

[
    'logical_operator' => <LogicalOperator>
    'sub_filters' => [
        <ColumnFilter>,
        <ColumnFilter>,
        <ColumnFilter>,
         // other conditions
        ]
    ]

Parameters

Name	Type	Description
logical_operator (required)	LogicalOperatorConst	A logical operator. Valid values: CONST_NOT (NOT), CONST_AND (AND), and CONST_OR (OR).
sub_filters (required)	array	The filters used in the logical operation. These can be SingleColumnValueFilter or CompositeColumnValueFilter.

Example

This example uses a range query to read rows with primary keys in the range [row1, row3) and applies a CompositeColumnValueFilter to filter the results.

$request = array (
    'table_name' => 'test_table',
    // Set the start primary key for the range query.
    'inclusive_start_primary_key' => array (
        array('id', 'row1')
    ),
    // Set the end primary key for the range query. The result does not include this key.
    'exclusive_end_primary_key'  => array (
        array('id', 'row3')
    ),
    // Read data in forward order.
    'direction' => DirectionConst::CONST_FORWARD,
    // Read the latest version of data.
    'max_versions' => 1
);

// Combine conditions: (col1 = val1 OR col2 = val2) AND (col3 = val3)
$request['column_filter'] = array(
    'logical_operator' => LogicalOperatorConst::CONST_AND,
    'sub_filters' => array(
        array(
            'logical_operator' => LogicalOperatorConst::CONST_OR,
            'sub_filters' => array(
                array(
                    'comparator' => ComparatorTypeConst::CONST_EQUAL,
                    'column_name' => 'col1',
                    'value' => 'val1'
                ),
                array(
                    'comparator' => ComparatorTypeConst::CONST_EQUAL,
                    'column_name' => 'col2',
                    'value' => 'val2'
                )
            )
        ),
        array(
            'comparator' => ComparatorTypeConst::CONST_EQUAL,
            'column_name' => 'col3',
            'value' => 'val3'
        )
    )
);

try {
    // Call getRange to read rows.
    $response = $client->getRange ($request);

    // Process the response.
    echo "* Read CU Cost: " . $response['consumed']['capacity_unit']['read'] . "\n";
    echo "* Write CU Cost: " . $response['consumed']['capacity_unit']['write'] . "\n";
    echo "* Row Data: " . "\n";
    foreach ($response['rows'] as $row) {
        echo json_encode($row) . "\n";
    }
} catch (Exception $e){
    echo "Get Range failed.";
}

References

Read a single row
Read rows in a range
Batch Read Data