Masterminds
Add GetOrderBy and RemoveOrderBy to get and remove the OrderByParts from the query
It is useful to be able to reset the order-by in some situations, or retrieve the order by. Our case is simple enough, we have a generic function that executes the query and returns a slice of objects, the total number of rows and an error object:
func QueryRowsPaginatedSq[T any](db Dbi, limit, offset uint64, builder squirrel.SelectBuilder) ([]T, int64, error)
It performs a count on the rows returned by the query, before applying the limit and offset. It does this by way of a cte wrapper over the original query: with count_cte as ( ... original query with order-by ) select count(*) from count_cte;
It then applies the limit and offset, and executes the query again, this time paginated.
Saving the order-by parts, then removing the order-by will allow the count(*) to execute faster, since its not concerned with ordering rows. Then on the second execution, I can apply the order-by, limit and offset. It's unfortunate that Postgresql does not perform this optimisation itself.
Why not use a windowing such as count(*) over() as total_rows? Because this performs worse in most situations (which could be related to the order-by again). Further, as I am using a Go generic function, I cannot create a new struct to scan into, that embeds to type T and TotalRows, as this is forbidden in Go, so we do the count and query as two steps.
This request is a very simple change.
I came across this older issue and faced a similar challenge. If I understood correctly, the OP wanted to reset the ORDER BY clause in their query builder. For future reference, here's a potential approach to achieve that:
If this is indeed op's case, to reset the order by, you can do the following:
s := builder.Delete(sq.SelectBuilder{}, "OrderByParts").(sq.SelectBuilder)
This solution is inspired by the Delete method from the ithub.com/lann/builder package, which is a dependency of Squirrel.
By removing the OrderByParts, you effectively reset the ORDER BY clause, allowing for more optimized COUNT(*) operations as the OP intended.
