I want to filter with two “independent” date ranges, i.e., start_date and end_date, something like in traditional SQL:
SELECT * FROM some_table
WHERE start_date <= NOW() AND end_date > NOW()
ORDER BY start_date ASC;
Although view query supports multiple keys, it does not work for two or more ranges “independently”: secondary key is ordered within first key.
While spatial(multi-dimensional) does, it really work for mutually independent start_date and end_date, HOWEVER, filtered result order looks strange:
for documents something like:
{
"id": 12345678,
"startDate": "2015-12-01 00:00:00",
"endDate": "2015-12-31 23:59:59",
"title": "product x"
}
with spatial view query,
function (doc) {
var start = Number(doc.startDate.replace(/[^0-9]/g, ''));
var end = Number(doc.endDate.replace(/[^0-9]/g, ''));
emit([start, end], [doc.startDate, doc.title]);
}
the result is(list startDate only for simplicity):
# python code something like:
# today = 20151220000000
# q = SpatialQuery()
# q.start_range = [20151101000000, today]
# q.end_range = [today, 20160101000000]
# view = View(bucket, 'my_design', 'my_spatial_view', query=q)
value=[u'2015-12-16 00:00:00', ...
value=[u'2015-11-30 00:00:00', ... # result range is correct, but not ordered :(
value=[u'2015-11-30 00:00:00', ...
value=[u'2015-12-16 00:00:00', ...
value=[u'2015-12-01 00:00:00', ...
value=[u'2015-12-07 00:00:00', ...
value=[u'2015-11-04 00:00:00', ...
it seems differ from view query map function: not sorted with natural ordering of 1st key.
What is default ordering for SPATIAL result?
I can’t find about ordering through (official) documents.