filter

README

Gin GORM filter

Scope function for GORM queries provides easy filtering with query parameters

Fork of https://github.com/ActiveChooN/gin-gorm-filter

Usage

go get github.com/magellancl/gin-gorm-filter_v2

Model definition

type UserModel struct {
	ID				uuid.UUID		`gorm:"type:uuid;primaryKey;default:gen_random_uuid()"`
	Username		string			`gorm:"uniqueIndex" filter:"filterable"`
	FullName		string			`filter:"param:full_name"`
	Role			string			`filter:"filterable"`
	CreatedAt		time.Time		`filter:"filterable"`
	UpdatedAt		time.Time		`filter:"filterable"`
}

param tag defines custom column name and query param name.

Controller Example

func GetUsers(c *gin.Context) {
	var users []UserModel
	var usersCount int64
	db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
	err := db.Model(&UserModel{}).Scopes(
		filter.FilterByQuery(c, filter.ALL),
	).Scan(&users).Error
	if err != nil {
		c.JSON(http.StatusBadRequest, err.Error())
		return
	}

	c.JSON(http.StatusOK, users)
}

Any filter combination can be used here filter.PAGINATION|filter.ORDER_BY e.g. Important note: GORM model should be initialized first for DB, otherwise filters won't work.

FILTER

Using the tag filter:"filterable" on your gorm object, and activating it with filter.FILTER, you can make a field filterable.

The standard filter will use this format : ?username=john.

You can use more complex filters with the separators <, >, >=, <=, !=. eg :

?created_at>=2022-10-18&created_at<2022-10-21 (be careful of your timezone. You should be able to input any date format readable by your DBMS)

?city!=grenoble

?price>10&created_at<2022-10-21

PAGINATE

Activating pagination with filter.PAGINATE will allow you to use the filters page and limit(eg : ?page=2&limit=50). Limit maximum is 100, so you can request a maximum of 100 items at once. The default value is 20. It will also renseign the following headers :

"X-Paginate-Items" -> total number of items
"X-Paginate-Pages" -> total number of pages
"X-Paginate-Current" -> current page
"X-Paginate-Limit" -> limit of items per page

ORDER BY

Request example

curl -X GET http://localhost:8080/users?page=1&limit=10&order_by=username&order_direction=asc&name=John

TODO list

• Write tests for the lib with CI integration
• Add ILIKE integration for PostgreSQL database
• Add other filters, like > or !=

Documentation

Index

• Constants
• Variables
• func FilterByQuery(c *gin.Context, config int) func(db *gorm.DB) *gorm.DB
• func FilterByQueryWithCustomDefault(c *gin.Context, config int, params QueryParams) func(db *gorm.DB) *gorm.DB
• func Paginate(c *gin.Context, db *gorm.DB, params QueryParams) *gorm.DB
• func ToSnakeCase(str string) string
• type QueryParams

Constants

const (
	//SEARCH   = 1  // NOT IMPLEMENTED // Filter response with LIKE query "search={search_phrase}"
	FILTER   = 2  // Filter response by column name values "{column_name}={value}"
	PAGINATE = 4  // Paginate response with page and page_size
	ORDER_BY = 8  // Order response by column name
	ALL      = 15 // Equivalent to SEARCH|FILTER|PAGINATE|ORDER_BY

)

Variables

var Separators = []string{
	gte,
	gt,
	lte,
	lt,
	neq,
	eq,
}

Functions

func FilterByQuery

func FilterByQuery(c *gin.Context, config int) func(db *gorm.DB) *gorm.DB

Filter DB request with query parameters. Note: Don't forget to initialize DB Model first, otherwise filter and search won't work Example:

db.Model(&UserModel).Scope(filter.FilterByQuery(ctx, filter.ALL)).Find(&users)

Or if only pagination and order is needed:

db.Model(&UserModel).Scope(filter.FilterByQuery(ctx, filter.PAGINATION|filter.ORDER_BY)).Find(&users)

And models should have appropriate`filter` tags:

type User struct {
	gorm.Model
	Username string `gorm:"uniqueIndex" filter:"param:login;searchable;filterable"`
	// `param` defines custom column name for the query param
	FullName string `filter:"searchable"`
}

func FilterByQueryWithCustomDefault

func FilterByQueryWithCustomDefault(c *gin.Context, config int, params QueryParams) func(db *gorm.DB) *gorm.DB

func Paginate

func Paginate(c *gin.Context, db *gorm.DB, params QueryParams) *gorm.DB

Use this function to paginate custom query. Example :

db := vd.db.Model(&Customer{}).

Select("id, COUNT(orders.id)").
Joins("JOIN orders ON orders.customer_id = customers.id").
Where("orders.type = ?", orderSold)

err = Paginate(c, db, params). Scan(&customType).Error

if err != nil {
	return ret, err
}

func ToSnakeCase

func ToSnakeCase(str string) string

Types

type QueryParams

type QueryParams struct {
	Filter         string `form:"filter"`
	Page           int    `form:"page,default=1"`
	Limit          int    `form:"limit,default=20"`
	All            bool   `form:"all,default=false"`
	OrderBy        string `form:"order_by,default=created_at"`
	OrderDirection string `form:"order_direction,default=desc,oneof=desc asc"`
}