CODEWITHSUNDEEP
couchbasesql++
Lho Ben
Lho Ben

Reputation: 2149

couchbase, Why this query return zero result

I am trying to select the latest created order for a given customer using this n1ql couchbase query :

select O1.* 
from `order` as O1 where
O1.creationDateTime = (select max(O2.creationDateTime)  from `order` O2 )

I get no result even thow :

select max(O2.creationDateTime) fromorderO2

return this result :

[
  {
    "$1": 1545569597891
  }
]

thanks for your help

Upvotes: 1

Views: 364

Answers (2)

vsr
vsr

Reputation: 7414

If you don't give RAW subquery results become Array of Objects which can't match with value. By going RAW it becomes array of values.

It looks like you want Document that has maximum creationDateTime. Try This.

SELECT RAW MAX([o1.creationDateTime, o1])[1]
FROM `order` AS o1
WHERE o1.creationDateTime IS NOT NULL;

MAX argument is ARRY of 2 elements. 1st is creationDateTime, 2nd is whole document. It try to MAX value of ARRAY i.e 1st value, if there is ties then second value. Eventually it projects 2nd value i.e. whole document.

This avoids scanning whole order twice.

If you want more efficient query.

Use Index Order.

CREATE INDEX ix1 ON `order`(creationDateTime DESC);

SELECT RAW o1
FROM `order` AS o1
WHERE o1.creationDateTime IS NOT NULL
ORDER BY o1.creationDateTime DESC LIMIT 1;

OR

Use Covered query decide which document key qualifies and then Fetch that document only.

SELECT o.* FROM (
     SELECT RAW MAX([o1.creationDateTime, META(o1).id])[1]
     FROM `order` AS o1
     WHERE o1.creationDateTime IS NOT NULL) AS o1 
JOIN `order` AS o  ON KEYS o1;

Upvotes: 2

Lho Ben
Lho Ben

Reputation: 2149

By adding 'in' (because the result is a list) with 'raw' (in order to get just the literal value) i resolved it :

select O1.* 
from `order` as O1 where
O1.creationDateTime in (select RAW max(O2.creationDateTime) as creationDateTime  from `order` O2 )

Upvotes: 2

Related Questions