How to use `distinct` in pagination

Hi
I want to paginate my data like this

SELECT COUNT(DISTINCT id) as total FROM table ....

but default paginate method always generates this query

SELECT COUNT(*) as total FROM table ....

How can I change paginate query. is it possible to extend QueryBuilder to achieve this using macros ?

any help. Thanks

1 Like

I would not be surprised if id is the primary key of your relational table: in that context both queries are the same because each id is naturally different from all others:

SELECT COUNT(DISTINCT id)

<=> ( is equivalent to)

SELECT COUNT(*)

actually the query has some inner joins so the result of count( distinct id ) is different than simple count(*) . believe me I’ve double checked everything.

In that case you should use a different approach, for example by grouping the result of the query by id

Yeah, my first approach was group by, but unfortunately paginate and group by has problems with each other. You can search on google and find out the problem. the result is more than 1 row, it has a row and total for each grouped id which is not what I want.

The query works well with either distinct or group by, but pagination has problem with both of them.

There should be a way to customize paginate method to change select of it.

1 Like

It can be an edge case and thus a bug ? Interesting issue … hope @virk or @romain.lanz could comment about it

1 Like