Tablestore:Sorting and paging

Scenarios

Index presorting

By default, data in a search index is sorted based on the presorting settings that are specified by the IndexSort parameter. When you use a search index to query data, the presorting settings that are specified by the IndexSort parameter determine the default order in which the matched data is returned.

When you create a search index, you can specify presorting settings by configuring the IndexSort parameter. If you do not specify presorting settings, data in the search index is sorted by primary key value.

Specify a sorting method when you query data

Sorting can be enabled only for fields for which the EnableSortAndAgg parameter is set to true.

You can specify a sorting method for each query. Search index-based queries support the following sorting methods. You can also specify multiple sorting methods based on different priorities.

ScoreSort

searchQuery := search.NewSearchQuery()
searchQuery.SetSort(&search.Sort{
    []search.Sorter{
        &search.ScoreSort{
            Order: search.SortOrder_DESC.Enum(), // Sort the query result in descending order of scores. 
        },
    },
})

PrimaryKeySort

You can use PrimaryKeySort to sort query results based on the primary key value.

searchQuery := search.NewSearchQuery()
searchQuery.SetSort(&search.Sort{
    []search.Sorter{
        &search.PrimaryKeySort{
            Order: search.SortOrder_ASC.Enum(),
        },
    },
})

FieldSort

You can use FieldSort to sort the query results based on the values of one or more specific columns.

// Sort the query results based on the values of the Col_Long column in descending order. 
searchQuery.SetSort(&search.Sort{
    []search.Sorter{
        &search.FieldSort{
            FieldName: "Col_Long",
            Order:     search.SortOrder_DESC.Enum(),
        },
    },
})

searchQuery.SetSort(&search.Sort{
    []search.Sorter{
        &search.FieldSort{
            FieldName: "col1",
            Order:     search.SortOrder_ASC.Enum(),
        },
        &search.FieldSort{
            FieldName: "col2",
            Order:     search.SortOrder_DESC.Enum(),
        },
    },
})

GeoDistanceSort

You can use GeoDistanceSort to sort query results by geographical location.

searchQuery.SetSort(&search.Sort{
    []search.Sorter{
        &search.GeoDistanceSort{
            FieldName: "location",   // Specify the name of the GEOPOINT field. 
            Points:    []string{"40,-70"}, // Specify the coordinate pair of the central point. 
        },
    },
})

Specify a paging method

You can configure the Limit and Offset parameters or use tokens to page the rows in the response.

Paging based on the Limit and Offset parameters

If you do not specify values for the Limit and Offset parameters, the default values are used. The default value of the Limit parameter is 10. The default value of the Offset parameter is 0.

searchQuery := search.NewSearchQuery()
searchQuery.SetLimit(10)
searchQuery.SetOffset(10) 

Paging based on tokens

We recommend that you use a token for deep paging because this method has no limits on the paging depth.

If Tablestore cannot read all data that meets the query conditions, Tablestore returns NextToken. You can use NextToken to continue reading the subsequent data.

By default, you can only page backward when you use a token. However, you can cache and use the previous token to page forward because a token is valid during the query.

When you use a token, the sorting method is the same as the method that is used in the previous request. Tablestore sorts data based on the IndexSort parameter by default or based on the method that you specified. You cannot specify the sorting method when you use a token. You cannot configure the Offset parameter when you use a token. Data is returned page by page in sequence. This results in a slow query.

/**
 * Use a token to read data by page. 
 * If NextToken is included in SearchResponse, you can use this token to initiate the next query. 
 * All data that meets the query conditions is returned if the value of NextToken is nil. 
 */
func QueryRowsWithToken(client *tablestore.TableStoreClient, tableName string, indexName string) {
    querys := []search.Query{
        &search.MatchAllQuery{},
        &search.TermQuery{
            FieldName: "Col_Keyword",
            Term:      "tablestore",
        },
    }
    for _, query := range querys {
        fmt.Printf("Test query: %#v\n", query)
        searchRequest := &tablestore.SearchRequest{}
        searchRequest.SetTableName(tableName)
        searchRequest.SetIndexName(indexName)
        searchQuery := search.NewSearchQuery()
        searchQuery.SetQuery(query)
        searchQuery.SetLimit(10)
        searchQuery.SetGetTotalCount(true)
        searchRequest.SetSearchQuery(searchQuery)
        searchResponse, err := client.Search(searchRequest)
        if err != nil {
            fmt.Printf("%#v", err)
            return
        }
        rows := searchResponse.Rows
        requestCount := 1
        for searchResponse.NextToken != nil {           
            {  
	              // If you want to persist NextToken or transfer NextToken to the frontend page, you can use Base64 to encode NextToken into a string. 
	              // Tokens are not strings. If you use string(NextToken) to encode a token into a string, information about the token is lost. 
	              tokenAsString := base64.StdEncoding.EncodeToString(searchResponse.NextToken)
	              // Decode the string into bytes. 
	              tokenAsByte, err := base64.StdEncoding.DecodeString(tokenAsString)
	              if err != nil {
		                fmt.Printf("len:%d, %#v",len(tokenAsByte), err)
		                return
	              }
            }
            searchQuery.SetToken(searchResponse.NextToken)
            searchResponse, err = client.Search(searchRequest)
            if err != nil {
                fmt.Printf("%#v", err)
                return
            }
            requestCount++
            for _, r := range searchResponse.Rows {
                rows = append(rows, r)
            }
        }
        fmt.Println("IsAllSuccess: ", searchResponse.IsAllSuccess)
        fmt.Println("TotalCount: ", searchResponse.TotalCount)
        fmt.Println("RowsSize: ", len(rows))
        fmt.Println("RequestCount: ", requestCount)
    }
}