filter

package module

v0.5.0 Latest Latest Go to latest Published: Sep 19, 2022 License: MIT Imports: 14 Imported by: 6

Details

Valid go.mod file
Redistributable license
Tagged version
Stable version
Learn more about best practices

Repository

github.com/go-goyave/filter

Links

Open Source Insights

README ¶

`filter` - Dynamic query params filters for Goyave

Compatible with Goyave v4 only

goyave.dev/filter allows powerful filtering using query parameters. Inspired by nestjsx/crud.

Usage

go get goyave.dev/filter

First, apply filters validation to the RuleSet used on the routes you wish the filters on.

import "goyave.dev/filter"

//...


var (
	IndexRequest = validation.RuleSet{}
)

func init() {
	filter.ApplyValidation(IndexRequest)
}

router.Get("/users", user.Index).Validate(user.IndexRequest)

Then implement your controller handler:

import "goyave.dev/filter"

//...

func Index(response *goyave.Response, request *goyave.Request) {
	var users []*model.User
	paginator, tx := filter.Scope(database.GetConnection(), request, &users)
	if response.HandleDatabaseError(tx) {
		response.JSON(http.StatusOK, paginator)
	}
}

And that's it! Now your front-end can add query parameters to filter as it wants.

You can also find records without paginating using ScopeUnpaginated():

var users []*model.User
tx := filter.ScopeUnpaginated(database.GetConnection(), request, &users)
if response.HandleDatabaseError(tx) {
	response.JSON(http.StatusOK, users)
}

Settings

You can disable certain features, or blacklist certain fields using filter.Settings:

settings := &filter.Settings{
	DisableFields: true, // Prevent usage of "fields"
	DisableFilter: true, // Prevent usage of "filter"
	DisableSort:   true, // Prevent usage of "sort"
	DisableJoin:   true, // Prevent usage of "join"

	FieldsSearch:   []string{"a", "b"},      // Optional, the fields used for the search feature
	SearchOperator: filter.Operators["$eq"], // Optional, operator used for the search feature, defaults to "$cont"

	Blacklist: filter.Blacklist{
		// Prevent selecting, sorting and filtering on these fields
		FieldsBlacklist: []string{"a", "b"},

		// Prevent joining these relations
		RelationsBlacklist: []string{"Relation"},

		Relations: map[string]*filter.Blacklist{
			// Blacklist settings to apply to this relation
			"Relation": &filter.Blacklist{
				FieldsBlacklist:    []string{"c", "d"},
				RelationsBlacklist: []string{"Parent"},
				Relations:          map[string]*filter.Blacklist{ /*...*/ },
				IsFinal:            true, // Prevent joining any child relation if true
			},
		},
	},
}
paginator, tx := settings.Scope(database.GetConnection(), request, &results)

Filter

?filter=field||$operator||value

Examples:

?filter=name||$cont||Jack (WHERE name LIKE "%Jack%")

You can add multiple filters. In that case, it is interpreted as an AND condition.

You can use OR conditions using ?or instead, or in combination:

?filter=name||$cont||Jack&or=name||$cont||John (WHERE (name LIKE %Jack% OR name LIKE "%John%"))
?filter=age||$eq||50&filter=name||$cont||Jack&or=name||$cont||John (WHERE ((age = 50 AND name LIKE "%Jack%") OR name LIKE "%John%"))

You can filter using columns from one-to-one relations ("has one" or "belongs to"):

?filter=Relation.name||$cont||Jack

If there is only one "or", it is considered as a regular filter:

?or=name||$cont||John (WHERE name LIKE "%John%")

If both "filter" and "or" are present, then they are interpreted as a combination of two AND groups compared with each other using OR:

?filter=age||$eq||50&filter=name||$cont||Jack&or=name||$cont||John&or=name||$cont||Doe
WHERE ((age = 50 AND name LIKE "%Jack%") OR (name LIKE "%John%" AND name LIKE "%Doe%"))

Note: All the filter conditions added to the SQL query are grouped (surrounded by parenthesis).

Operators


`$eq`	`=`, equals
`$ne`	`<>`, not equals
`$gt`	`>`, greater than
`$lt`	`<`, lower than
`$gte`	`>=`, greater than or equals
`$lte`	`<=`, lower than or equals
`$starts`	`LIKE val%`, starts with
`$ends`	`LIKE %val`, ends with
`$cont`	`LIKE %val%`, contains
`$excl`	`NOT LIKE %val%`, not contains
`$in`	`IN (val1, val2,...)`, in (accepts multiple values)
`$notin`	`NOT IN (val1, val2,...)`, in (accepts multiple values)
`$isnull`	`IS NULL`, is NULL (doesn't accept value)
`$notnull`	`IS NOT NULL`, not NULL (doesn't accept value)
`$between`	`BETWEEN val1 AND val2`, between (accepts two values)

Search

Search is similar to multiple or=column||$cont||value, but the column and operator are specified by the server instead of the client.

Specify the column using Settings:

settings := &filter.Settings{
	FieldsSearch: []string{"a", "b"},
	SearchOperator: filter.Operators["$eq"], // Optional, defaults to "$cont"
	//...
}

?search=John (WHERE (a LIKE "%John%" OR b LIKE "%John%"))

If you don't specify FieldsSearch, the query will search in all selectable fields.

Fields / Select

?fields=field1,field2

A comma-separated list of fields to select. If this field isn't provided, uses SELECT *.

Sort

?sort=column,ASC|DESC

Examples:

?sort=name,ASC
?sort=age,DESC

You can also sort by multiple fields.

?sort=age,DESC&sort=name,ASC

Join

?join=relation

Preload a relation. You can also only select the columns you need:

?join=relation||field1,field2

You can join multiple relations:

?join=profile||firstName,email&join=notifications||content&join=tasks

Pagination

Internally, goyave.dev/filter uses Goyave's Paginator.

?page=1&per_page=10

If page isn't given, the first page will be returned.
If per_page isn't given, the default page size will be used. This default value can be overridden by changing filter.DefaultPageSize.
Either way, the result is always paginated, even if those two parameters are missing.

Computed columns

Sometimes you need to work with a "virtual" column that is not stored in your database, but is computed using an SQL expression. A dynamic status depending on a date for example. In order to support the features of this library properly, you will have to add the expression to your model using the computed struct tag:

type MyModel struct{
	ID uint
	// ...
	StartDate time.Time
	Status string `gorm:"->"` `computed:"CASE WHEN ~~~ct~~~.start_date < NOW() THEN 'pending' ELSE 'started' END"` 
}

Note: the ~~~ct~~~ is an indicator for the current table. It will be replaced by the correct table or relation name automatically. This allows the usage of computed fields in relations too, where joins are needed.

Tip: you can also use composition to avoid including the virtual column into your model:

type MyModel struct{
	ID uint
	// ...
	StartDate time.Time
}

type MyModelWithStatus struct{
	MyModel
	Status string `gorm:"->"` `computed:"CASE WHEN ~~~ct~~~.start_date < NOW() THEN 'pending' ELSE 'started' END"` 
}

Security

Inputs are escaped to prevent SQL injections.
Fields are pre-processed and clients cannot request fields that don't exist. This prevents database errors. If a non-existing field is required, it is simply ignored. The same goes for sorts and joins. It is not possible to request a relation that doesn't exist.
Foreign keys are always selected in joins to ensure associations can be assigned to parent model.
Be careful with bidirectional relations (for example an article is written by a user, and a user can have many articles). If you enabled both your models to preload these relations, the client can request them with an infinite depth (Articles.User.Articles.User...). To prevent this, it is advised to use the relation blacklist or IsFinal on the deepest requestable models. See the settings section for more details.

Tips

Model recommendations

Use json:",omitempty" on all model fields.
- Note: using omitempty on slices will remove them from the json result if they are not nil and empty. There is currently no solution to this problem using the standard json package.
Use json:"-" on foreign keys.
Use *null.Time from the gopkg.in/guregu/null.v4 library instead of sql.NullTime.
Always specify gorm:"foreignKey", otherwise falls back to "ID".
Don't use gorm.Model and add the necessary fields manually. You get better control over json struct tags this way.
Use pointers for nullable relations and nullable fields that implement sql.Scanner (such as null.Time).

Static conditions

If you want to add static conditions (not automatically defined by the library), it is advised to group them like so:

users := []model.User{}
db := database.GetConnection()
db = db.Where(db.Session(&gorm.Session{NewDB: true}).Where("username LIKE ?", "%Miss%").Or("username LIKE ?", "%Ms.%"))
paginator, tx := filter.Scope(db, request, &users)
if response.HandleDatabaseError(tx) {
	response.JSON(http.StatusOK, paginator)
}

Custom operators

You can add custom operators (or override existing ones) by modifying the filter.Operators map:

import (
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
	"goyave.dev/filter"
	"goyave.dev/goyave/v4/util/sqlutil"
)

// ...

filter.Operators["$cont"] = &filter.Operator{
	Function: func(tx *gorm.DB, filter *filter.Filter, column string, dataType schema.DataType) *gorm.DB {
		query := column + " LIKE ?"
		value := "%" + sqlutil.EscapeLike(filter.Args[0]) + "%"
		return filter.Where(tx, query, value)
	},
	RequiredArguments: 1,
}

License

Documentation ¶

Index ¶

Variables
func ApplyValidation(set validation.RuleSet)
func ApplyValidationRules(set *validation.Rules)
func Scope(db *gorm.DB, request *goyave.Request, dest interface{}) (*database.Paginator, *gorm.DB)
func ScopeUnpaginated(db *gorm.DB, request *goyave.Request, dest interface{}) *gorm.DB
type Blacklist
type Filter
- func ParseFilter(filter string) (*Filter, error)
- func (f *Filter) Scope(settings *Settings, sch *schema.Schema) (func(*gorm.DB) *gorm.DB, func(*gorm.DB) *gorm.DB)
- func (f *Filter) Where(tx *gorm.DB, query string, args ...interface{}) *gorm.DB
type Join
- func ParseJoin(join string) (*Join, error)
- func (j *Join) Scopes(settings *Settings, schema *schema.Schema) []func(*gorm.DB) *gorm.DB
type Operator
type Search
- func (s *Search) Scope(schema *schema.Schema) func(*gorm.DB) *gorm.DB
type Settings
- func (s *Settings) Scope(db *gorm.DB, request *goyave.Request, dest interface{}) (*database.Paginator, *gorm.DB)
- func (s *Settings) ScopeUnpaginated(db *gorm.DB, request *goyave.Request, dest interface{}) *gorm.DB
type Sort
- func ParseSort(sort string) (*Sort, error)
- func (s *Sort) Scope(settings *Settings, schema *schema.Schema) func(*gorm.DB) *gorm.DB
type SortOrder

Constants ¶

This section is empty.

Variables ¶

View Source

var (
	// DefaultPageSize the default pagination page size if the "per_page" query param
	// isn't provided.
	DefaultPageSize = 10
)

View Source

var (
	// Operators definitions. The key is the query representation of the operator, (e.g. "$eq").
	Operators = map[string]*Operator{
		"$eq":  {Function: basicComparison("="), RequiredArguments: 1},
		"$ne":  {Function: basicComparison("<>"), RequiredArguments: 1},
		"$gt":  {Function: basicComparison(">"), RequiredArguments: 1},
		"$lt":  {Function: basicComparison("<"), RequiredArguments: 1},
		"$gte": {Function: basicComparison(">="), RequiredArguments: 1},
		"$lte": {Function: basicComparison("<="), RequiredArguments: 1},
		"$starts": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				query := column + " LIKE ?"
				value := sqlutil.EscapeLike(filter.Args[0]) + "%"
				return filter.Where(tx, query, value)
			},
			RequiredArguments: 1,
		},
		"$ends": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				query := column + " LIKE ?"
				value := "%" + sqlutil.EscapeLike(filter.Args[0])
				return filter.Where(tx, query, value)
			},
			RequiredArguments: 1,
		},
		"$cont": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				query := column + " LIKE ?"
				value := "%" + sqlutil.EscapeLike(filter.Args[0]) + "%"
				return filter.Where(tx, query, value)
			},
			RequiredArguments: 1,
		},
		"$excl": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				query := column + " NOT LIKE ?"
				value := "%" + sqlutil.EscapeLike(filter.Args[0]) + "%"
				return filter.Where(tx, query, value)
			},
			RequiredArguments: 1,
		},
		"$in":    {Function: multiComparison("IN"), RequiredArguments: 1},
		"$notin": {Function: multiComparison("NOT IN"), RequiredArguments: 1},
		"$isnull": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				return filter.Where(tx, column+" IS NULL")
			},
			RequiredArguments: 0,
		},
		"$istrue": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				if dataType != schema.Bool {
					return tx
				}
				return filter.Where(tx, column+" IS TRUE")
			},
			RequiredArguments: 0,
		},
		"$isfalse": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				if dataType != schema.Bool {
					return tx
				}
				return filter.Where(tx, column+" IS FALSE")
			},
			RequiredArguments: 0,
		},
		"$notnull": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				return filter.Where(tx, column+" IS NOT NULL")
			},
			RequiredArguments: 0,
		},
		"$between": {
			Function: func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB {
				query := column + " BETWEEN ? AND ?"
				return filter.Where(tx, query, filter.Args[0], filter.Args[1])
			},
			RequiredArguments: 2,
		},
	}
)

View Source

var Separator = "||"

Separator the separator used when parsing the query

Functions ¶

func ApplyValidation ¶

func ApplyValidation(set validation.RuleSet)

ApplyValidation add all fields used by the filter module to the given RuleSet.

func ApplyValidationRules ¶

func ApplyValidationRules(set *validation.Rules)

ApplyValidationRules add all fields used by the filter module to the given *Rules.

func Scope ¶

func Scope(db *gorm.DB, request *goyave.Request, dest interface{}) (*database.Paginator, *gorm.DB)

Scope using the default FilterSettings. See `FilterSettings.Scope()` for more details.

func ScopeUnpaginated ¶ added in v0.5.0

func ScopeUnpaginated(db *gorm.DB, request *goyave.Request, dest interface{}) *gorm.DB

ScopeUnpaginated using the default FilterSettings. See `FilterSettings.ScopeUnpaginated()` for more details.

Types ¶

type Blacklist ¶

type Blacklist struct {
	Relations map[string]*Blacklist

	// FieldsBlacklist prevent the fields in this list to be selected or to
	// be used in filters and sorts.
	FieldsBlacklist []string
	// RelationsBlacklist prevent joining the relations in this list.
	RelationsBlacklist []string

	// IsFinal if true, prevent joining any relation
	IsFinal bool
}

Blacklist definition of blacklisted relations and fields.

type Filter ¶

type Filter struct {
	Field    string
	Operator *Operator
	Args     []string
	Or       bool
}

Filter structured representation of a filter query.

func ParseFilter ¶

func ParseFilter(filter string) (*Filter, error)

ParseFilter parse a string in format "field||$operator||value" and return a Filter struct. The filter string must satisfy the used operator's "RequiredArguments" constraint, otherwise an error is returned.

func (*Filter) Scope ¶

func (f *Filter) Scope(settings *Settings, sch *schema.Schema) (func(*gorm.DB) *gorm.DB, func(*gorm.DB) *gorm.DB)

Scope returns the GORM scope to use in order to apply this filter.

func (*Filter) Where ¶

func (f *Filter) Where(tx *gorm.DB, query string, args ...interface{}) *gorm.DB

Where applies a condition to given transaction, automatically taking the "Or" filter value into account.

type Join ¶

type Join struct {
	Relation string
	Fields   []string
	// contains filtered or unexported fields
}

Join structured representation of a join query.

func ParseJoin ¶

func ParseJoin(join string) (*Join, error)

ParseJoin parse a string in format "relation||field1,field2,..." and return a Join struct.

func (*Join) Scopes ¶

func (j *Join) Scopes(settings *Settings, schema *schema.Schema) []func(*gorm.DB) *gorm.DB

Scopes returns the GORM scopes to use in order to apply this joint.

type Operator ¶

type Operator struct {
	Function          func(tx *gorm.DB, filter *Filter, column string, dataType schema.DataType) *gorm.DB
	RequiredArguments uint8
}

Operator used by filters to build the SQL query. The operator function modifies the GORM statement (most of the time by adding a WHERE condition) then returns the modified statement. Operators may need arguments (e.g. "$eq", equals needs a value to compare the field to); RequiredArguments define the minimum number of arguments a client must send in order to use this operator in a filter. RequiredArguments is checked during Filter parsing. Operators may return the given tx without change if they don't support the given dataType.

type Search ¶ added in v0.2.0

type Search struct {
	Query    string
	Operator *Operator
	Fields   []string
}

Search structured representation of a search query.

func (*Search) Scope ¶ added in v0.2.0

func (s *Search) Scope(schema *schema.Schema) func(*gorm.DB) *gorm.DB

Scope returns the GORM scopes with the search query.

type Settings ¶

type Settings struct {
	// FieldsSearch allows search for these fields
	FieldsSearch []string
	// SearchOperator is used by the search scope, by default it use the $cont operator
	SearchOperator *Operator

	Blacklist

	// DisableFields ignore the "fields" query if true.
	DisableFields bool
	// DisableFilter ignore the "filter" query if true.
	DisableFilter bool
	// DisableSort ignore the "sort" query if true.
	DisableSort bool
	// DisableJoin ignore the "join" query if true.
	DisableJoin bool
	// DisableSearch ignore the "search" query if true.
	DisableSearch bool
}

Settings settings to disable certain features and/or blacklist fields and relations.

func (*Settings) Scope ¶

func (s *Settings) Scope(db *gorm.DB, request *goyave.Request, dest interface{}) (*database.Paginator, *gorm.DB)

Scope apply all filters, sorts and joins defined in the request's data to the given `*gorm.DB` and process pagination. Returns the resulting `*database.Paginator` and the `*gorm.DB` result, which can be used to check for database errors. The given request is expected to be validated using `ApplyValidation`.

func (*Settings) ScopeUnpaginated ¶ added in v0.5.0

func (s *Settings) ScopeUnpaginated(db *gorm.DB, request *goyave.Request, dest interface{}) *gorm.DB

ScopeUnpaginated apply all filters, sorts and joins defined in the request's data to the given `*gorm.DB` without any pagination. Returns the `*gorm.DB` result, which can be used to check for database errors. The records will be added in the given `dest` slice. The given request is expected to be validated using `ApplyValidation`.

type Sort ¶

type Sort struct {
	Field string
	Order SortOrder
}

Sort structured representation of a sort query.

func ParseSort ¶

func ParseSort(sort string) (*Sort, error)

ParseSort parse a string in format "name,ASC" and return a Sort struct. The element after the comma (sort order) must have a value allowing it to be converted to SortOrder, otherwise an error is returned.

func (*Sort) Scope ¶

func (s *Sort) Scope(settings *Settings, schema *schema.Schema) func(*gorm.DB) *gorm.DB

Scope returns the GORM scope to use in order to apply sorting.

type SortOrder ¶

type SortOrder string

SortOrder the allowed strings for SQL "ORDER BY" clause.

const (
	// SortAscending "ORDER BY column ASC"
	SortAscending SortOrder = "ASC"
	// SortDescending "ORDER BY column DESC"
	SortDescending SortOrder = "DESC"
)

Source Files ¶

View all Source files

?	: This menu
/	: Search site
f or F	: Jump to
y or Y	: Canonical URL

README ¶

filter - Dynamic query params filters for Goyave

Usage

Settings

Filter

Operators

Search

Fields / Select

Sort

Join

Pagination

Computed columns

Security

Tips

Model recommendations

Static conditions

Custom operators

License

Documentation ¶

Index ¶

Constants ¶

Variables ¶

Functions ¶

func ApplyValidation ¶

func ApplyValidationRules ¶

func Scope ¶

func ScopeUnpaginated ¶ added in v0.5.0

Types ¶

type Blacklist ¶

type Filter ¶

func ParseFilter ¶

func (*Filter) Scope ¶

func (*Filter) Where ¶

type Join ¶

func ParseJoin ¶

func (*Join) Scopes ¶

type Operator ¶

type Search ¶ added in v0.2.0

func (*Search) Scope ¶ added in v0.2.0

type Settings ¶

func (*Settings) Scope ¶

func (*Settings) ScopeUnpaginated ¶ added in v0.5.0

type Sort ¶

func ParseSort ¶

func (*Sort) Scope ¶

type SortOrder ¶

Source Files ¶

`filter` - Dynamic query params filters for Goyave