ORDER BY not working in named query

Question

I've got a named query that has an ORDER BY clause:

`query OrdersByRequesterSort {
  description: "Select all orders by requester"
  statement:
      SELECT org.test.sample.Order
          WHERE (requester == _$requesterParam)
            ORDER BY [placeTimestamp DESC]
}`

which I patterened after a query that I saw here:

https://hyperledger.github.io/composer/reference/query-language.html

I'm getting the following error when I attempt to execute this query:

`
    {
  "error": {
    "statusCode": 500,
    "name": "Error",
    "message": "Error trying to query chaincode. Error: chaincode error (status: 500, message: Error: http: read on closed response body)",
    "stack": "Error: Error trying to query chaincode. Error: chaincode error (status: 500, message: Error: http: read on closed response body)\n    at channel.queryByChaincode.then.catch (/Users/bower/.nvm/versions/node/v6.3.0/lib/node_modules/composer-rest-server/node_modules/composer-connector-hlfv1/lib/hlfconnection.js:779:34)"
  }
}`

Am I doing something wrong? Is this supported?

Thanks in advance.

Answer 1

It looks like you need to define a sort index on your data types in CouchDB to use ORDER BY.

See this for details: https://github.com/hyperledger/composer/issues/1640

Answer 2

For my network, I order the query returned from HistorianRecord by the timestamp:

query getAllHistorianRecords {
  description: "getTradeRelatedHistorianRecords"
  statement: 
    SELECT org.hyperledger.composer.system.HistorianRecord
      WHERE (transactionTimestamp > '0000-01-01T00:00:00.000Z')
        ORDER BY [transactionTimestamp ASC]
}

To make this ORDER BY statement work I had to create an index in CouchDB. If you used the scripts to create your fabric your database should be at http://localhost:5984/_utils/#database/composerchannel/_all_docs.

curl -X POST --header 'Content-Type: application/json' --header 
'Accept: application/json' -d '
{
  "index": {
    "fields": [
      {
        "data.transactionTimestamp": "asc"
      }
    ]
  },
  "type": "json"
}' 'http://localhost:5984/composerchannel/_index'

This curl will create an index that the query can use to enable ORDER BY