Getting Count for Query with Filters, Order etc.

[trentassaya]

I'm trying to get a count for an existing query (using .Count()) for a query that has several where conditions applied for user-defined filtering, ordering (for user-defined sorting), etc. It also uses a simple offset pagination, which is why I can't simply get a length of the results.

I cannot seem to figure out how to do it, despite it originally thinking it would be very simple.

The query looks something like this (shortened example) :

query := models.Users.Query(
	ctx, db,
	sm.Distinct(),
	sm.InnerJoin(...),
	sm.InnerJoin(...),
	sm.InnerJoin(...),
	sm.InnerJoin(...),
	sm.Where(...),
	sm.Where(...),
	sm.Offset(...),
)

Originally I thought I could just get the count, then apply some extra query mods to get the paginated results. Something like this:

users, err := query.Count()

query.Apply(sm.Columns(..., ..., etc)
query.Apply(sm.OrderBy(models.UserColumns.Name))
query.Apply(sm.Limit(50))

users, err := query.All()

However, this order doesn't work, as the count is not replaced with the column names when calling .All() like I thought it would be after applying the sm.Columns query mod. So I end up with q query like this after calling .All():

SELECT DISTINCT  count(1), "users"."name" FROM "users" AS "users" ORDER BY "users"."name" LIMIT 50 OFFSET 0

which results in the error column users.name must appear in the GROUP BY clause or be used in the aggregate function, which makes sense.

So I tried it the other way around (get the results first, then get the count). It works in this order without the order by, but as soon as I add the order by to the results query (which is first in this case) I get the same error.

I'm not sure if I'm not understanding something, or if I should be doing this a completely different way, or if there is a problem with how .Count() is modifying (or not modifying) the query.

Any insight would be appreciated.

[stephenafamo]

I have considered removing the Count method since it may not work how you would expect.

What Count does is overwrite any selected columns with SELECT COUNT(1) and add .... LIMIT 1 before making the query. And since this modifies the original query, it can lead to unexpected issues like this.

One solution would be to clone the query before issuing a Count that way the original query is not modified at all. We could then do other things like drop the ORDER BY which would have no effect on such a query anyway.

[trentassaya]

Thanks for the direction! What I ended up doing is this (query is the original query):

count := query.Clone()
count.Expression.SetSelect(psql.Raw(fmt.Sprintf("COUNT( DISTINCT %s)", models.UserColumns.ID)))
count.Expression.SetLimit(nil)
count.Expression.OrderBy = clause.OrderBy{}

which seems to give me what I was looking for.

Although I feel like there is a SetOrderBy method that is missing (to go with all the other SetXxx methods. Not sure if that was intentional for some reason, or just something that got overlooked. I see there is an AppendOrder, but that doesn't allow removing or replacing a currently-applied ORDER BY.