qp

package module
v0.0.0-...-05fc847 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 22, 2024 License: Apache-2.0 Imports: 8 Imported by: 0

README

QP - Query Parser from URL Query to SQL

Query Parser (QP) is a Go library designed to streamline the creation of dynamic SQL queries for web applications. It provides an easy-to-use API for handling filtering through GET queries and manages translations and validations for user inputs.

Installation

To install the QP library, run the following command:

go get -u github.com/nex-gen-tech/qp

Idea

The inspiration for this library came from the article "REST API Design: Filtering, Sorting, and Pagination." The concepts in the article are useful for projects that involve lists with various filtering options.

Quick Start

Refer to the examples folder and the test files for more comprehensive examples.

package main

import (
	"errors"
	"fmt"
	"net/url"

	"github.com/nex-gen-tech/qp"
)

func main() {
	url, _ := url.Parse("http://localhost/?sort=name,-id&limit=10&id=1&i[eq]=5&s[eq]=one&email[like]=*tim*|name[like]=*tim*")
	q, _ := qp.NewParse(url.Query(), qp.Validations{
		"limit:required": qp.MinMax(10, 100), // limit must be present and between 10 and 100
		"sort":           qp.In("id", "name"),   // sort could be "id" or "name"
		"s":              qp.In("one", "two"),   // filter: s - string with equal comparison
		"id:int":         nil,                   // filter: id is an integer without additional validation
		"i:int": func(value interface{}) error { // custom validation function for "i"
			if value.(int) > 1 && value.(int) < 10 {
				return nil
			}
			return errors.New("i: must be greater than 1 and less than 10")
		},
		"email": nil,
		"name":  nil,
	})

	fmt.Println(q.SQL("table")) // SELECT * FROM table WHERE id = ? AND i = ? AND s = ? AND (email LIKE ? OR name LIKE ?) ORDER BY name, id DESC LIMIT 10
	fmt.Println(q.Where())      // id = ? AND i = ? AND s = ? AND (email LIKE ? OR name LIKE ?)
	fmt.Println(q.Args())       // [1 5 one %tim% %tim%]

	q.AddValidation("fields", rqp.In("id", "name"))
	q.SetUrlString("http://localhost/?fields=id,name&limit=10")
	q.Parse()

	fmt.Println(q.SQL("table")) // SELECT id, name FROM table ORDER BY id LIMIT 10
	fmt.Println(q.Select())     // id, name
	fmt.Println(q.Args())       // []
}

Top-Level Fields

  • fields: Specifies the fields for the SELECT clause, separated by commas (","). For example, &fields=id,name. If not provided, "*" is used by default. To use this filter, define a validation function. Use rqp.In("id", "name") to limit the fields in your query.
  • sort: Specifies the sorting fields list, separated by commas (","). This field must be validated. A +/- prefix indicates ASC/DESC sorting. For example, &sort=+id,-name results in ORDER BY id, name DESC. Validate fields using rqp.In("id", "name").
  • limit: Specifies the limit for the LIMIT clause. The value should be greater than 0 by default. Validation for limit is optional. Use rqp.Max(100) to set an upper threshold.
  • offset: Specifies the offset for the OFFSET clause. The value should be greater than or equal to 0 by default. Validation for offset is optional.
Example URL Queries
  • ?fields=id,name&sort=-created_at&limit=20&offset=5
  • ?sort=name,-id&limit=50&status[eq]=active&category[like]=tech
  • ?fields=product_id,product_name&sort=price&price[gt]=100&in_stock[eq]=true

Validation Modifiers

  • :required: Indicates that the parameter is required and must be present in the query string. Raises an error if not found.
  • :int: Specifies that the parameter must be convertible to an integer type. Raises an error if not.
  • :bool: Specifies that the parameter must be convertible to a boolean type. Raises an error if not.

Supported Types

  • string: The default type for all filters unless specified otherwise. Can be compared using eq, ne, gt, lt, gte, lte, like, ilike, nlike, nilike, in, nin, is, not methods. (nlike, nilike represent NOT LIKE, NOT ILIKE; in, nin represent IN, NOT IN; and is, not are for NULL comparisons: IS NULL, IS NOT NULL.)
  • int: Integer type. Must be specified with the tag ":int". Can be compared using eq, ne, gt, lt, gte, lte, in, nin methods.
  • bool: Boolean type. Must be specified with the tag ":bool". Can be compared using the eq method.

Date Usage

Here's an example demonstrating how to handle date values:

import (
	"fmt"
	"net/url"
	validation "github.com/go-ozzo/ozzo-validation/v4"
)

func main() {
	url, _ := url.Parse("http://localhost/?created_at[eq]=2020-10-02")
	q, _ := rqp.NewParse(url.Query(), rqp.Validations{
		"created_at": func(v interface{}) error {
			s, ok := v.(string)
			if !ok {
				return rqp.ErrBadFormat
			}
			return validation.Validate(s, validation.Date("2006-01-02"))
		},
	})

	q.ReplaceNames(rqp.Replacer{"created_at": "DATE(created_at)"})

	fmt.Println(q.SQL("table")) // SELECT * FROM table WHERE DATE(created_at) = ?
}

Advanced Usage

QP offers advanced features like custom validation functions, handling multiple OR conditions, and more.

Custom Validation Functions

You can define custom validation functions to perform specific checks on query parameters.

func validateAge(value interface{}) error {
	age, ok := value.(int)
	if !ok {
		return rqp.ErrBadFormat
	}
	if age < 18 {
		return errors.New("age must be at least 18")
	}
	return nil
}
Handling Multiple OR Conditions

QP supports handling multiple OR conditions within filters.

q.AddORFilters(func(query *rqp.Query) {
	query.AddFilter("name", rqp.LIKE, "John")
	query.AddFilter("email", rqp.LIKE, "john@example.com")
})
Replacing Field Names

You can replace field names in filters, fields, and sorting using the ReplaceNames method.

q.ReplaceNames(rqp.Replacer{"created_at": "DATE(created_at)"})

Guide for Frontend Developers

License

This project is licensed under the MIT License.

Contributing

Contributions are welcome! Please open a pull request or issue on the GitHub repository to suggest improvements or report any issues.

Acknowledgments

Special thanks to the contributors and supporters of this project.

Documentation

Index

Examples

Constants

View Source 
const NULL = "NULL"

NULL constant

Variables

View Source 
var (
	ErrRequired           = NewError("required")
	ErrBadFormat          = NewError("bad format")
	ErrEmptyValue         = NewError("empty value")
	ErrUnknownMethod      = NewError("unknown method")
	ErrNotInScope         = NewError("not in scope")
	ErrSimilarNames       = NewError("similar names of keys are not allowed")
	ErrMethodNotAllowed   = NewError("method are not allowed")
	ErrFilterNotAllowed   = NewError("filter are not allowed")
	ErrFilterNotFound     = NewError("filter not found")
	ErrValidationNotFound = NewError("validation not found")
)

Errors list:

Functions

This section is empty.

Types

type Error 

type Error struct {
	// contains filtered or unexported fields
}

Error special rqp.Error type

func NewError 

func NewError(msg string) *Error

NewError constructor for internal errors

func (*Error) Error 

func (e *Error) Error() string

type Filter 

type Filter struct {
	Key    string // key from URL (eg. "id[eq]")
	Name   string // name of filter, takes from Key (eg. "id")
	Method Method // compare method, takes from Key (eg. EQ)
	Value  interface{}
	OR     StateOR
}

Filter represents a filter defined in the query part of URL

func (*Filter) Args 

func (f *Filter) Args() ([]interface{}, error)

Args returns arguments slice depending on filter condition

func (*Filter) Where 

func (f *Filter) Where() (string, error)

Where returns condition expression

type Method 

type Method string

Method is a compare method type 

var (
	EQ     Method = "EQ"
	NE     Method = "NE"
	GT     Method = "GT"
	LT     Method = "LT"
	GTE    Method = "GTE"
	LTE    Method = "LTE"
	LIKE   Method = "LIKE"
	ILIKE  Method = "ILIKE"
	NLIKE  Method = "NLIKE"
	NILIKE Method = "NILIKE"
	IS     Method = "IS"
	NOT    Method = "NOT"
	IN     Method = "IN"
	NIN    Method = "NIN"
)

Compare methods:

type Query 

type Query struct {
	Fields  []string
	Offset  int
	Limit   int
	Sorts   []Sort
	Filters []*Filter

	Error error
	// contains filtered or unexported fields
}

Query the main struct of package

func New 

func New() *Query

New creates new instance of Query

func NewParse 

func NewParse(q url.Values, v Validations) (*Query, error)

NewParse creates new Query instance and Parse it

func NewQV 

func NewQV(q url.Values, v Validations) *Query

NewQV creates new Query instance with parameters

func (*Query) AddField 

func (q *Query) AddField(field string) *Query

AddField adds field to SELECT statement

func (*Query) AddFilter 

func (q *Query) AddFilter(name string, m Method, value interface{}) *Query

AddFilter adds a filter to Query

func (*Query) AddFilterRaw 

func (q *Query) AddFilterRaw(condition string) *Query

AddFilterRaw adds a filter to Query as SQL condition. This function supports only single condition per one call. If you'd like add more then one conditions you should call this func several times.

func (*Query) AddORFilters 

func (q *Query) AddORFilters(fn func(query *Query)) *Query

AddORFilters adds multiple filter into one `OR` statement inside parenteses. E.g. (firstname ILIKE ? OR lastname ILIKE ?)

Example 
q := New().AddFilter("test", EQ, "ok")
q.AddORFilters(func(query *Query) {
	query.AddFilter("firstname", ILIKE, "*hello*")
	query.AddFilter("lastname", ILIKE, "*hello*")
})
q.SQL("table") // SELECT * FROM table WHERE test = ? AND (firstname ILIKE ? OR lastname ILIKE ?)

Output:

func (*Query) AddSortBy 

func (q *Query) AddSortBy(by string, desc bool) *Query

AddSortBy adds an ordering rule to Query

func (*Query) AddValidation 

func (q *Query) AddValidation(NameAndTags string, v ValidationFunc) *Query

AddValidation adds a validation to Query

func (*Query) Args 

func (q *Query) Args() []interface{}

Args returns slice of arguments for WHERE statement

func (*Query) Clone 

func (q *Query) Clone() *Query

Clone makes copy of Query

func (*Query) FieldsString 

func (q *Query) FieldsString() string

FieldsString returns elements list separated by comma (",") for querying in SELECT statement or a star ("*") if nothing provided

Return example:

When "fields" empty or not provided: `*`.

When "fields=id,email": `id, email`.

func (*Query) GetFilter 

func (q *Query) GetFilter(name string) (*Filter, error)

GetFilter returns filter by name

func (*Query) HaveField 

func (q *Query) HaveField(field string) bool

HaveField returns true if request asks for specified field

func (*Query) HaveFilter 

func (q *Query) HaveFilter(name string) bool

HaveFilter returns true if request contains some filter

func (*Query) HaveSortBy 

func (q *Query) HaveSortBy(by string) bool

HaveSortBy returns true if request contains sorting by specified in by field name

func (*Query) IgnoreUnknownFilters 

func (q *Query) IgnoreUnknownFilters(i bool) *Query

IgnoreUnknownFilters set behavior for Parser to raise ErrFilterNotAllowed to undefined filters or not

func (*Query) LIMIT 

func (q *Query) LIMIT() string

LIMIT returns word LIMIT with number

Return example: ` LIMIT 100`

func (*Query) OFFSET 

func (q *Query) OFFSET() string

OFFSET returns word OFFSET with number

Return example: ` OFFSET 0`

func (*Query) ORDER 

func (q *Query) ORDER() string

ORDER returns words ORDER BY with list of elements for sorting you can use +/- prefix to specify direction of sorting (+ is default, apsent is +)

Return example: ` ORDER BY id DESC, email`

func (*Query) Order 

func (q *Query) Order() string

Order returns list of elements for ORDER BY statement you can use +/- prefix to specify direction of sorting (+ is default) return example: `id DESC, email`

func (*Query) Parse 

func (q *Query) Parse() (err error)

Parse parses the query of URL as query you can use standart http.Request query by r.URL.Query()

func (*Query) RemoveFilter 

func (q *Query) RemoveFilter(name string) error

RemoveFilter removes the filter by name

func (*Query) RemoveValidation 

func (q *Query) RemoveValidation(NameAndOrTags string) error

RemoveValidation remove a validation from Query You can provide full name of filter with tags or only name of filter: RemoveValidation("id:int") and RemoveValidation("id") are equal

func (*Query) ReplaceNames 

func (q *Query) ReplaceNames(r Replacer)

ReplaceNames replace all specified name to new names Sometimes we've to hijack properties, for example when we do JOINs, so you can ask for filter/field "user_id" but replace it with "users.user_id". Parameter is a map[string]string which means map[currentName]newName. The library provide beautiful way by using special type rqp.Replacer. Example: 

  rqp.ReplaceNames(rqp.Replacer{
	   "user_id": "users.user_id",
  })

func (*Query) SELECT 

func (q *Query) SELECT() string

SELECT returns word SELECT with fields from Filter "fields" separated by comma (",") from URL-Query or word SELECT with star ("*") if nothing provided

Return examples:

When "fields" empty or not provided: `SELECT *`.

When "fields=id,email": `SELECT id, email`.

func (*Query) SQL 

func (q *Query) SQL(table string) string

SQL returns whole SQL statement

func (*Query) Select 

func (q *Query) Select() string

Select returns elements list separated by comma (",") for querying in SELECT statement or a star ("*") if nothing provided

Return examples:

When "fields" empty or not provided: `*`

When "fields=id,email": `id, email`

func (*Query) SetDelimiterIN 

func (q *Query) SetDelimiterIN(d string) *Query

SetDelimiterIN sets delimiter for values of filters

func (*Query) SetDelimiterOR 

func (q *Query) SetDelimiterOR(d string) *Query

SetDelimiterOR sets delimiter for OR filters in query part of URL

func (*Query) SetLimit 

func (q *Query) SetLimit(limit int) *Query

SetLimit sets Offset of query

func (*Query) SetOffset 

func (q *Query) SetOffset(offset int) *Query

SetOffset sets Offset of query

func (*Query) SetUrlQuery 

func (q *Query) SetUrlQuery(query url.Values) *Query

SetUrlQuery change url in the Query for parsing uses when you need provide Query from http.HandlerFunc(w http.ResponseWriter, r *http.Request) you can do q.SetUrlValues(r.URL.Query())

func (*Query) SetUrlString 

func (q *Query) SetUrlString(Url string) error

SetUrlString change url in the Query for parsing uses when you would like to provide raw URL string to parsing

func (*Query) SetValidations 

func (q *Query) SetValidations(v Validations) *Query

SetValidations change validations rules for the instance

func (*Query) WHERE 

func (q *Query) WHERE() string

WHERE returns list of filters for WHERE SQL statement with `WHERE` word

Return example: ` WHERE id > 0 AND email LIKE 'some@email.com'`

func (*Query) Where 

func (q *Query) Where() string

Where returns list of filters for WHERE statement return example: `id > 0 AND email LIKE 'some@email.com'`

type Replacer 

type Replacer map[string]string

Replacer struct for ReplaceNames method

type Sort 

type Sort struct {
	By   string
	Desc bool
}

Sort is ordering struct

type StateOR 

type StateOR byte
const (
	NoOR StateOR = iota
	StartOR
	InOR
	EndOR
)

type ValidationFunc 

type ValidationFunc func(value interface{}) error

ValidationFunc represents a validator for Filters.

func In 

func In(validValues ...interface{}) ValidationFunc

In checks if the value is within the provided values.

func Max 

func Max(max int) ValidationFunc

Max checks if the value is less than or equal to the maximum.

func Min 

func Min(min int) ValidationFunc

Min checks if the value is greater than or equal to the minimum.

func MinMax 

func MinMax(min, max int) ValidationFunc

MinMax checks if the value is between or equal to the minimum and maximum.

func Multi 

func Multi(validators ...ValidationFunc) ValidationFunc

Multi combines multiple validation functions. Usage: Multi(Min(10), Max(100)).

func NotEmpty 

func NotEmpty() ValidationFunc

NotEmpty checks if the string value is not empty.

type Validations 

type Validations map[string]ValidationFunc

Validations type replacement for map. Used in NewParse(), NewQV(), SetValidations().

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.