Query Filtering & Pagination

Dokumentasi fungsi filtering, sorting, dan pagination.

GetNew Function

func GetNew(
    model interface{}, 
    sort string, 
    filter string, 
    page int, 
    perPage int,
) (interface{}, int, int, error)

Parameters

Parameter	Type	Deskripsi
model	interface	Pointer ke slice model
sort	string	Sorting configuration
filter	string	Filter configuration
page	int	Nomor halaman (1-based)
perPage	int	Jumlah item per halaman

Return Values

Return	Type	Deskripsi
data	interface	Hasil query (slice model)
totalCount	int	Total jumlah data
totalPages	int	Total halaman
error	error	Error jika ada

Sorting

Format

field,order

Parameters

Parameter	Nilai	Deskripsi
field	column name	Nama kolom untuk sorting
order	asc / desc	Ascending atau Descending

Examples

// Sort by name ascending
sort := "nama,asc"

// Sort by created_at descending
sort := "created_at,desc"

// Sort by single field (default asc)
sort := "nama"

Default Sorting

Jika sort kosong, default sorting adalah:

ORDER BY COALESCE(updated_at, created_at) DESC

Filtering

Format

[column,operator,value;column,operator,value;...]

Supported Operators

Operator	SQL Equivalent	Deskripsi
=	= value	Equal (default)
>	> value	Greater than
<	< value	Less than
>=	>= value	Greater than or equal
<=	<= value	Less than or equal
!=	!= value	Not equal
like	LIKE value	Pattern match (case-sensitive)
ilike	ILIKE value	Pattern match (case-insensitive)

Examples

// Single filter - exact match
filter := "[status,=,active]"

// Multiple filters
filter := "[status,=,active;tipe_pegawai,=,pns]"

// Pattern matching (case-insensitive)
filter := "[nama,ilike,%john%]"

// Comparison
filter := "[created_at,>=,2024-01-01]"

// Not equal
filter := "[status,!=,deleted]"

SQL Generated

filter := "[nama,ilike,%john%;status,=,active;created_at,>=,2024-01-01]"

Menghasilkan:

WHERE nama ILIKE '%john%' 
  AND status = 'active' 
  AND created_at >= '2024-01-01'

Pagination

Calculation

offset := (page - 1) * perPage
totalPages := ceil(totalCount / perPage)

Example

Total Data	Page	PerPage	Offset	Total Pages
100	1	10	0	10
100	2	10	10	10
100	5	20	80	5
55	3	10	20	6

Complete Usage Example

Controller

func GetPengguna(c *gin.Context) {
    // Parse query parameters
    page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
    perPage, _ := strconv.Atoi(c.DefaultQuery("perPage", "10"))
    sort := c.Query("sort")       // e.g., "nama,asc"
    filter := c.Query("filter")   // e.g., "[status,=,active]"
    
    var pengguna []models.Pengguna
    data, totalCount, totalPages, err := repository.GetNew(
        &pengguna,
        sort,
        filter,
        page,
        perPage,
    )
    
    if err != nil {
        helpers.SendError(c, helpers.NewInternalError("Failed to fetch data"))
        return
    }
    
    c.JSON(http.StatusOK, helpers.ResponseData[[]models.Pengguna]{
        Code:    200,
        Status:  true,
        Message: "Data retrieved successfully",
        Info: helpers.Info{
            Page:       page,
            PerPage:    perPage,
            TotalPages: totalPages,
            TotalData:  totalCount,
        },
        Data: data.([]models.Pengguna),
    })
}

API Request

GET /api/v1/pengguna?page=1&perPage=10&sort=nama,asc&filter=[tipe_pegawai,=,pns;status,=,active]

API Response

{
    "code": 200,
    "status": true,
    "message": "Data retrieved successfully",
    "info": {
        "page": 1,
        "perPage": 10,
        "totalPages": 5,
        "totalData": 45
    },
    "data": [
        {
            "id": "...",
            "nama": "John Doe",
            "tipe_pegawai": "pns",
            "status": "active"
        },
        ...
    ]
}

Filter Parser

Implementation

func parseFilterString(filterString string) (map[string]string, error) {
    if filterString == "" {
        return nil, nil
    }

    // Remove brackets
    filterString = strings.TrimPrefix(filterString, "[")
    filterString = strings.TrimSuffix(filterString, "]")

    filters := make(map[string]string)
    for _, filterPart := range strings.Split(filterString, ";") {
        keyValuePair := strings.Split(filterPart, ",")
        if len(keyValuePair) != 3 {
            return nil, fmt.Errorf("invalid filter format: %s", filterPart)
        }
        column, operator, value := keyValuePair[0], keyValuePair[1], keyValuePair[2]
        filters[column] = value + "," + operator
    }
    return filters, nil
}

Error Handling

// Invalid format - will return error
filter := "[nama,john]"  // Missing operator

// Valid format
filter := "[nama,=,john]"

Best Practices

1. Validate Input

   if page < 1 {
       page = 1
   }
   if perPage < 1 || perPage > 100 {
       perPage = 10
   }

2. Limit PerPage untuk mencegah query besar

   maxPerPage := 100
   if perPage > maxPerPage {
       perPage = maxPerPage
   }

3. Index Database Columns yang sering di-filter/sort

4. URL Encode Filter jika mengandung special characters

   filter=%5Bnama%2Cilike%2C%25john%25%5D