Add GetOrderBy and RemoveOrderBy to get and remove the OrderByParts from the query

[Cofgren]

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.

[alanwgt]

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.