V8-archive: Need a `total_filter`

[cdwmhcc]

Feature Request

If limit is applied, the total number of filtered results can be returned
Need a total_filter

What problem does this feature solve?

There is no way to paginate the results without getting the total number of filtered items.

Need a `total_filter' paginates the results of the items

ex:

Search the title like chrome data in the collection soft ,display 10 items per page.There are 16 chrome-related data in the database,

let pagesize = 10
client.getItems("soft",{
    meta:"total_count,result_count",
    sort: filterParams.sort,
    offset: Number(params.page) * pagesize - pagesize,
    limit: pagesize,
    filter: {
      title: {
        contains: "chrome"
      }
    },
    status: "published"
 })

Return result：

data:{
  data: [
     ……
  ],
  meta:{
      result_count: 10
      total_count: 322
  }
}

But it returns 322 (the total number of soft collections)

Not 16 (total number of filtered items)

Solutions I use temporarily

async asyncData({ app, params, error, payload }) {
    let pagesize = 10
    let filterParams = {
      sort: "-created_on",
      searchQuery: "chrome",
    }  
    if(filterParams.searchQuery){
      const [filterlist, softlist] = await Promise.all([

        client.getItems("soft",{
          meta:"result_count",
          fields: "id",
          filter: {
            title: {
              contains: filterParams.searchQuery
            }
          },
          status: "published"
        }),

        client.getItems("soft",{
          meta:"total_count,result_count",
          sort: filterParams.sort,
          offset: Number(params.page) * pagesize - pagesize,
          limit: pagesize,
          filter: {
            title: {
              contains: filterParams.searchQuery
            }
          },
          status: "published"
        })
      ])
      console.log('data',softlist)
      return {
        softlist: softlist.data,
        filter_total: filterlist.meta.result_count,
        total: softlist.meta.total_count,
        currentPage: Number(params.page),
        pagesize: pagesize
    }else{
      const [softlist] = await Promise.all([

        client.getItems("soft",{
          meta:"total_count,result_count",
          sort: filterParams.sort,
          offset: Number(params.page) * pagesize - pagesize,
          limit: pagesize,
          filter: {
            title: {
              contains: filterParams.searchQuery
            }
          },
          status: "published"
        })
      ])
      return {
        softlist: softlist.data,
        filter_total: null,
        total: softlist.meta.total_count,
        currentPage: Number(params.page),
        pagesize: pagesize
      }
    }
  }

<el-pagination
          v-show="total > pagesize"
          layout="prev, pager, next"
          @current-change="handlePageChange"
          :page-size="pagesize"
          :current-page.sync="currentPage"
          :total="filter_total ? filter_total : total">
</el-pagination>

How do you think this should be implemented?

Need a total_filter

Would you be willing to work on this?

I don't seem to be able to help.

[shealavington]

Unfortunately, due to this being more complex than just "adding a filter", this request may take longer than expected.

This has been briefly discussed with the team.

We have an idea that needs benchmark testing.

The API would make a separate query (for the same response) against the data when the filter_total meta is requested. This will return only a count of the items. This is likely the most efficient solution, as it would be completed by MySQL.

[cdwmhcc]

Unfortunately, due to this being more complex than just "adding a filter", this request may take longer than expected.

This has been briefly discussed with the team.

We have an idea that needs benchmark testing.

The API would make a separate query (for the same response) against the data when the filter_total meta is requested. This will return only a count of the items. This is likely the most efficient solution, as it would be completed by MySQL.

    client.getItems("soft",{
          meta:"total_filter,total_count,result_count",
          offset: 0,
          limit: 10,
          filter: {
            title: {
              contains: 'chrome'
            }
          },
          status: "published"
    })

Return result：

data:{
  data: [
     ……
  ],
  meta:{
      result_count: 10,
      total_count: 16,
      total_count: 322
  }
}

[rijkvanzanten]

@cdwmhcc when you have:

{
  "filter": {
    "title": {
      "contains": "chrome"
    },
    "meta": "result_count"
}

it means that the API will do the following query:

SELECT *, COUNT(*) AS result_count FROM 'soft' WHERE title = 'chrome';

result_count will always count _with filters_.

In order to get the full count, we would have to do 2 queries:

SELECT *, COUNT(*) AS result_count FROM 'soft' WHERE title = 'chrome';
/* with filter                                 ^^^^^^^^^^^^^^^^^^^^^^^ */

/* and: */
SELECT COUNT(*) FROM 'soft';
/* to get the count without the filter */

This will most likely result in a big performance regression on bigger collections.

[cdwmhcc]

@cdwmhcc when you have:

{
  "filter": {
    "title": {
      "contains": "chrome"
    },
    "meta": "result_count"
}

it means that the API will do the following query:

SELECT *, COUNT(*) AS result_count FROM 'soft' WHERE title = 'chrome';

result_count will always count _with filters_.

In order to get the full count, we would have to do 2 queries:

SELECT *, COUNT(*) AS result_count FROM 'soft' WHERE title = 'chrome';
/* with filter                                 ^^^^^^^^^^^^^^^^^^^^^^^ */

/* and: */
SELECT COUNT(*) FROM 'soft';
/* to get the count without the filter */

This will most likely result in a big performance regression on bigger collections.

What I don't quite understand is why you return total instead of filter total when you use filter filtering.
What is its actual user usage scenario? Can you give me an example?
I think respond filter total, not total.

Maybe I haven't fully understood what you mean yet.

[benhaynes]

I am going to close this as a duplicate of: https://github.com/directus/api/issues/673

_But_, I agree with this feature. I think we should return these as optional metadata:

• result_count (on by default)
• filtered_count (off by default)
• total_count or collection_count (off by default)

A second query to get the total item count shouldn't be too bad if we're not returning anything more than the count. Also, we shouldn't be concerned with collection size... this is very useful if someone has 832 items in a table, and they should _expect_ it to be slow if there are 2,043,910+ items.