ArangoDb - How to count number of filtered results before limiting them

Question

db.query(aql `
   FOR doc IN ${collection} 
     FILTER REGEX_TEST(CONCAT(VALUES(doc, true)), ${queryStr}, true)
       SORT doc[${sortBy}] ${dir}
         LIMIT ${start, count} 
           RETURN doc._key
        `)
        .then(cursor => {
            cb(cursor._result)
        }, err => console.log(err))

I have above AQL query, I want to count total nuber of filtered results before limiting the results per page (For Pagination Purpose)

I think issue is similar to this MySQL - How to count rows before pagination?, Find total number of results in mySQL query with offset+limit

want to do in ArangoDb with AQL

and part of solution may be this How to count number of elements with AQL?

So, What is the efficient/best solution for my requirement with AQL ?

Capelin

Did the answer fullfill your needs? If not, whats missing? If, can you mark it accepted? – Ouse

One more help please.. :) would you please give me a minimal example for how to acheive the above using ArangoJS – Capelin

Answer 1

You can set the flag fullCount in the options for creating the cursor to true. Then the result will have an extra attribute with the sub-attributes stats and fullCount.

You then can get the the fullCount-attribute via cursor.extra.stats.fullCount. This attribute contains the number of documents in the result before the last LIMIT in the query was applied. see HTTP documentation

In addition, you should use the explain feature to analyse your query. In your case, your query will always make a full collection scan, thus won't scale well.

update

I added the fullCount flag to your code. Keep in mind, that the fullCount attribute only appears if the number of results before LIMIT is higher then the results after.

db.query(aql ` 
  FOR doc IN ${collection} 
    FILTER REGEX_TEST(CONCAT(VALUES(doc, true)), ${queryStr}, true)
      SORT doc[${sortBy}] ${dir}
        LIMIT ${start, count} 
          RETURN {family: doc.family, group: doc.group} `, {count:true, options:{fullCount:true} })
.then(cursor => { console.log(cursor) }, err => console.log(err))

Dineen

One more help please.. :) would you please give me a minimal example for how to acheive the above using ArangoJS – Capelin

How can i set flags in this.. db.query(aql ` FOR doc IN ${collection} FILTER REGEX_TEST(CONCAT(VALUES(doc, true)), ${queryStr}, true) SORT doc[${sortBy}] ${dir} LIMIT ${start, count} RETURN {family: doc.family, group: doc.group} `, {count:true}).then(cursor => { console.log(cursor) }, err => console.log(err)) – Capelin

How to set count flag for above? – Capelin

{count:true} in above code does nothing.. :( So what is the correct way to set flag and get fullcount from above code...? – Capelin

the newes version removed the options param and instead you can add the fullCount just like count for ex: {count: true, fullCount: true} as 2nd param of db.query – Deicer