Paginating with n1ql

SQL++
19

Thank you @geraldss.

The new index and query worked for me. I have total 3 queries on these type of documents.

Q1. SELECT meta().id FROM cms WHERE -_createdAt < 0 AND _type=‘Product’ AND _active=true ORDER BY -_createdAt LIMIT 100 OFFSET 0;

Q2. SELECT meta().id FROM cms WHERE -_createdAt < 0 AND brand=‘1389’ AND _type=‘Product’ AND _active=true ORDER BY -_createdAt LIMIT 100 OFFSET 0;

Q3. SELECT meta().id FROM cms WHERE _type = “Product” AND _active = TRUE AND ANY category IN categories SATISFIES category = “3748” END AND brand = “1389” AND -_createdAt < 0 ORDER BY -_createdAt LIMIT 100 OFFSET 0;

where brand and category is input parameter and it can be anything. Also here categories is of type Array. So I created these 3 indexes.

I1 . CREATE INDEX prod_created_idx ON cms((-_createdAt),_type,_active, meta().id) WHERE ((_type = “Product”) and (_active = true));

I2. CREATE INDEX prod_brand_created_idx ON cms(brand,(-_createdAt),_type,_active) WHERE ((_type = “Product”) and (_active = true));

I3. CREATE INDEX prod_cat_brand_createdidx ON cms((distinct (array category for category in categories end)),categories,brand,(-_createdAt),_type,_active) WHERE ((_type = “Product”) and (_active = true));

I get the expected results in milli seconds which is expected time. I also tried deleting first 2 indexes (
I1 and I2 ) and keeping only 3rd one (I3 ), But It does not worked for first 2 queries. So I created all the 3 indexes.

However I am missing a sort count (only in Q1) int metrics which i was getting in earlier queries.

I also need count of these type of document.

CQ1 SELECT count(meta().id) FROM cms WHERE _type = “Product” AND _active = TRUE;

and to do so I created another index CI1 for count

CI1 CREATE INDEX prod_count_idx ON cms((meta().id)) WHERE ((_type = “Product”) and (_active = true));

My document Structure is as follow

“cms”: {
“_active”: true,
“_createdAt”: 1469801004787,
“_createdBy”: “admin”,
“_modifiedAt”: 1469801004787,
“_modifiedBy”: “admin”,
“_type”: “Product”,
“attributes”: {
“2576”: “6”,
“type-of-product”: “Key Type Drill Chuck”,
“uom”: “piece”
},
“brand”: “948”,
“categories”: [
“3748”
],
},
“id”: “Product::MA.CH.MA.326662”
}

Please help me to understand ,

  1. Why i am missing sort count in 1st query Q1.
  2. Is there better way to query and get the metrics for total count as well.
  3. Is there better way to create index only for count.

Please explain.

Thanks
Amar

1 Like