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 ?

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))