pref: Optimize select query performance when there is no limit and offset

[Tiscs]

If there is no limit and offset, we can use a single query to get the count and scan, the additional count query is not necessary.

// If there is no limit and offset 
bundb.NewSelect().Model(users).Where("age > ?", 59).ScanAndCount(ctx)
// SELECT user.id, user.name, user.age FROM users WHERE user.age > 59;

// Otherwise
bundb.NewSelect().Model(users).Where("age > ?", 59).Offset(0).Limit(10).ScanAndCount(ctx)
// SELECT user.id, user.name, user.age FROM users WHERE user.age > 59;
// SELECT COUNT(*) FROM users WHERE user.age > 59;

In this way, we can use the more general repos layer method definition, like this:

type UsersRepo interface {
    // Implemention:
    //
    // var users []*models.User
    // query := r.bdb.NewSelect().Model(&users)
    // for _, mod := range mods {
    //     query = query.Apply(mod)
    // }
    // return query.ScanAndCount(ctx)
    //
    // Example 1:
    //
    //   users, _, err := usersRepo.FindUsersOlderThan(ctx, 59)
    //
    // Example 2:
    //
    //   users, total, err := usersRepo.FindUsersOlderThan(ctx, 59, func(sq *bun.SelectQuery) *bun.SelectQuery {
    //     return sq.Offset(0).Limit(10)
    //   })
    FindUsersOlderThan(ctx context.Context, age int, mods ...func(*bun.SelectQuery) *bun.SelectQuery) ([]*models.User, int, error)
}

[vmihailenco]

Nice, thanks!