goext/sq/paginate.go

127 lines
4.1 KiB
Go
Raw Permalink Normal View History

2024-01-13 21:36:47 +01:00
package sq
import (
"context"
"fmt"
ct "gogs.mikescher.com/BlackForestBytes/goext/cursortoken"
"gogs.mikescher.com/BlackForestBytes/goext/exerr"
"gogs.mikescher.com/BlackForestBytes/goext/langext"
pag "gogs.mikescher.com/BlackForestBytes/goext/pagination"
)
type PaginateFilter interface {
SQL(params PP) (filterClause string, joinClause string, joinTables []string)
Sort() []FilterSort
}
type FilterSort struct {
Field string
Direction ct.SortDirection
}
func Paginate[TData any](ctx context.Context, q Queryable, table string, filter PaginateFilter, scanMode StructScanMode, scanSec StructScanSafety, page int, limit *int) ([]TData, pag.Pagination, error) {
prepParams := PP{}
sortOrder := filter.Sort()
sortCond := ""
if len(sortOrder) > 0 {
sortCond = "ORDER BY "
for i, v := range sortOrder {
if i > 0 {
sortCond += ", "
}
sortCond += v.Field + " " + string(v.Direction)
}
}
pageCond := ""
if limit != nil {
pageCond += fmt.Sprintf("LIMIT :%s OFFSET :%s", prepParams.Add(*limit+1), prepParams.Add(*limit*(page-1)))
}
filterCond, joinCond, joinTables := filter.SQL(prepParams)
selectCond := table + ".*"
for _, v := range joinTables {
selectCond += ", " + v + ".*"
}
sqlQueryData := "SELECT " + selectCond + " FROM " + table + " " + joinCond + " WHERE ( " + filterCond + " ) " + sortCond + " " + pageCond
sqlQueryCount := "SELECT " + "COUNT(*)" + " FROM " + table + " " + joinCond + " WHERE ( " + filterCond + " ) "
rows, err := q.Query(ctx, sqlQueryData, prepParams)
if err != nil {
return nil, pag.Pagination{}, exerr.Wrap(err, "failed to list paginated entries from DB").Str("table", table).Any("filter", filter).Int("page", page).Any("limit", limit).Build()
}
entities, err := ScanAll[TData](ctx, q, rows, scanMode, scanSec, true)
if err != nil {
return nil, pag.Pagination{}, exerr.Wrap(err, "failed to decode paginated entries from DB").Str("table", table).Int("page", page).Any("limit", limit).Str("scanMode", string(scanMode)).Str("scanSec", string(scanSec)).Build()
}
if page == 1 && (limit == nil || len(entities) <= *limit) {
return entities, pag.Pagination{
Page: 1,
Limit: langext.Coalesce(limit, len(entities)),
TotalPages: 1,
TotalItems: len(entities),
CurrentPageCount: 1,
}, nil
} else {
countRows, err := q.Query(ctx, sqlQueryCount, prepParams)
if err != nil {
return nil, pag.Pagination{}, exerr.Wrap(err, "failed to query total-count of paginated entries from DB").Str("table", table).Build()
}
if !countRows.Next() {
2024-01-14 00:07:01 +01:00
return nil, pag.Pagination{}, exerr.New(exerr.TypeSQLDecode, "SQL COUNT(*) query returned no rows").Str("table", table).Any("filter", filter).Build()
2024-01-13 21:36:47 +01:00
}
var countRes int
err = countRows.Scan(&countRes)
if err != nil {
return nil, pag.Pagination{}, exerr.Wrap(err, "failed to decode total-count of paginated entries from DB").Str("table", table).Build()
}
if len(entities) > *limit {
entities = entities[:*limit]
}
paginationObj := pag.Pagination{
Page: page,
Limit: langext.Coalesce(limit, countRes),
TotalPages: pag.CalcPaginationTotalPages(countRes, langext.Coalesce(limit, countRes)),
TotalItems: countRes,
CurrentPageCount: len(entities),
}
return entities, paginationObj, nil
}
}
func Count(ctx context.Context, q Queryable, table string, filter PaginateFilter) (int, error) {
prepParams := PP{}
filterCond, joinCond, _ := filter.SQL(prepParams)
sqlQueryCount := "SELECT " + "COUNT(*)" + " FROM " + table + " " + joinCond + " WHERE ( " + filterCond + " )"
countRows, err := q.Query(ctx, sqlQueryCount, prepParams)
if err != nil {
return 0, exerr.Wrap(err, "failed to query count of entries from DB").Str("table", table).Build()
}
if !countRows.Next() {
2024-01-14 00:07:01 +01:00
return 0, exerr.New(exerr.TypeSQLDecode, "SQL COUNT(*) query returned no rows").Str("table", table).Any("filter", filter).Build()
2024-01-13 21:36:47 +01:00
}
var countRes int
err = countRows.Scan(&countRes)
if err != nil {
return 0, exerr.Wrap(err, "failed to decode count of entries from DB").Str("table", table).Build()
}
return countRes, nil
}