How to do Order by using SUBSTR in Couchbase Lite iOS 2.6.3?

Mobile Couchbase Lite
1

Hi There,
I have a following a document list and I have a following N1QL query and I need to build an equivalent Query Builder in iOS but I don’t know how to do with ORDER By with multiple conditions.

N1QL Query:

SELECT * from fruits WHERE type = ‘fruits’ ORDER By SUBSTR(createdDate,0,10) DESC, percentChange DESC
LIMIT 10 OFFSET 0

[

{
"name" : "Apple",
"percentChange" : 20,
"type" : "fruits"
"createdDate": "2019-12-04T05:44:12.127Z",

},
{
"name" : "Orange",
"percentChange" : -20,
"type" : "fruits"
"createdDate": "2019-12-04T04:44:12.127Z"

},
{
"name" : "Apricot",
"percentChange" : 10,
"type" : "fruits"
"createdDate": "2019-12-02T04:44:12.127Z"

},
{
"name" : "Avocado",
"percentChange" : -0.1,
"type" : "fruits"
"createdDate": "2019-12-04T04:44:12.127Z"

}
]
2
1 Like
3

@vsr1 Thank you but here i want to do SUBSTR on date key and then do a sort order. can you please suggest me?

4

We don’t support substring extraction (we have trim, upper, lower) . You can check if a string has a specific substring but that wouldn’t help you. As far as date manipulation functions, this is what we support today.

5

@priya.rajagopal Thank you for your response. So we cannot achieve a query builder which is equivalent as I mentioned N1QL?

6

Probably not. Your options are to

  • Update the createdBy date format or add a different property to only specify the day.
  • Handle this within you app layer. Once you get the query results ,iterate over it (it would be sorted anyways) and group by day.