result.All() to return TotalCount of rows natively

[VojtechVitek]

Since some databases support counting total rows via Window Functions that are inexpensive, we may think of supporting this in upper/db v2:

PostgreSQL: SELECT COUNT(1) OVER() AS _total_count, * FROM users LIMIT 10 OFFSET 10;

In databases where this is not supported, we'd have to make two queries: SELECT COUNT(1) as _total_count FROM users; SELECT * FROM users LIMIT 10 OFFSET 10;

Proposal:

var people []Person

res = col.Find(db.Cond{"name": "Max"}).Limit(10).Skip(100).Sort("-last_name")

count, err = res.All(&people).TotalCount()
// count == 2000
// len(people) == 10, since we used limit 10

Alternative syntax (optional second argument):

err = res.All(&people, &count)

Alternative syntax no.2

res.TotalCount(&count).All(&people)

Note: Can we do something similar for Total number of affected rows?

[xiam]

This is database specific, so we won't be able to add it to Find(), but we can add something like this for the SQL builder, which is database specific, I'm not sure of the syntax though, I'm sure that after using the builder we'll come up with a nice idea.

[solojavier]

Is the SQL Builder something that already exists? Or something that needs to be worked on?

[xiam]

Hey @solojavier, the SQL builder already exists, we have some working examples here: https://upper.io/db.v2/lib/sqlbuilder

[solojavier]

So the count feature can be added by using the SQLBuilder? I'm trying to understand what is required to resolve this issue...