How to change count method query?

[matheuscamarques]

#811

I'm working with paginate and I realized that the count(*) method is too slow for the amount of data I have, I would like to run this query to count the data, could I change the method that calls the paginate count?

SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table_name';

I want that when I call builder.paginate(page,itemsPerPage) internally call my custom count query, got it?

Default count is too slow there is more than 900k data in the table

[Julien-R44]

You can add a method to the model query builder like this: https://jagr.co/lessons/how-to-add-a-custom-method-to-the-model-query-builder-in-adonisjs

Otherwise just use a raw query :
https://docs.adonisjs.com/reference/database/query-client#raw-query

[Julien-R44]

On how to do what? I advise you to just use raw queries, it will do the trick and it's easier.
If you really want to extend the model query builder you just have to follow the tutorial everything is explained 🙂

[matheuscamarques]

I want that when I call builder.paginate(page,itemsPerPage) internally call my custom count query, got it?

[matheuscamarques]

Default count is too slow there is more than 900k data in the table

[Julien-R44]

Ah, it's for the pagination. AFAIK there is no way to customize the way the total count is retrieved. You'll have to write a paginator yourself until Adonis supports this feature.

[matheuscamarques]

I had to get creative but this worked for me.

const rows = await builder.orderBy(orderBy).forPage(page + 1, itemsPerPage)
    const total = await Database.query().select("reltuples AS estimate")
    .from('pg_class')
    .where('relname', 'view_programa')
    .first()

    const paginatedRows: ModelPaginatorContract<ViewPrograma> = new ModelPaginator<ViewPrograma>(total.estimate,itemsPerPage,page +1,...rows)