queryp

README

QueryP

NOTE: This is under development and is not yet officially released. Expect breaking changes for a bit.

This is a Go library for generating (mostly) database agnostic query parameters with:

• Filter
• Sort
• Limit/Offsets

Parsing

The library also supports parsing a string with a specific format into the query parameters. It's most useful as a library for parsing GET query parameters.

For example:

field1=value1&(field2=value2|field3=value3)&limit=10&offset=10&sort=name,-description

Get all things where field1=value1 AND ( field2=value2 OR field3=value3 ) sort by name ascending, description descending limit to 10 records and skip the first 10

Supported Operators:

* &		- Logic AND (as well as splitting operators)
* |		- Logic OR

* =     - Exactly Equals
* !=    - Not Equals
* < 	- Less Than
* >		- Greater Than
* <=	- Less than or equals
* >=	- Greater than or equals
* =~	- Like (supports % wildcards)
* !=~   - Not Like (supports % wildcards)
* =~~   - Like case-insensitive (supports % wildcards)
* !=~~	- Not Like case-insensitive (supports % wildcards)
* :		- Regular Expression Match
* !:	- Not Regular Expression Match
* :~	- Regular Expression Match (case-insensitive
* !:~	- Not Regular Expression Match (case-insensitive

* ()	- Parenthesis can be use for precedence.

Drivers

Database drivers are built into the package

• qppg - Postgres syntax driver

Usage

queryString := "field1=value1&(field2=value2|field3=value3)&limit=10&offset=10&sort=name,-description"

qp, err := queryp.ParseQuery(queryString)
if err != nil {
	log.Fatal(err)
}

var queryClause strings.Builder
var queryParams = []interface{}{}

// Fields we are allowed to filter by
filterFields := queryp.FilterFieldTypes{
	"table_name.thing_id":          queryp.FilterTypeSimple, // Simple matching good for equal/not equal
	"table_name.thing_name":        queryp.FilterTypeString, // Matching with like and regexp
	"table_name.created":           queryp.FilterTypeTime,   // Matching based on times
	"table_name.exists":            queryp.FilterTypeBool,   // Supports true/false only
	"table_name.quantity":          queryp.FilterTypeNumeric,// Supports numeric comparisons
	"someotherfield":               queryp.FilterFieldCustom{FieldName: "whoathird", FilterType: queryp.FilterTypeString}, // Use one field name in URL and another for database field
}

// Fields we are allowed to sort by
sortFields := queryp.SortFields{
	"source.source_id",
	"source.source_name",
}

// Default sort if none specified
if len(qp.Sort) == 0 {
	qp.Sort = queryp.Sort{queryp.SortTerm{Field: "source.source_name", Desc: false}}
}

// If we will filter
if len(qp.Filter) > 0 {
	queryClause.WriteString(" WHERE ")
}

// Generate the WHERE criteria for postgres
if err := qppg.FilterQuery(filterFields, qp.Filter, &queryClause, &queryParams); err != nil {
	return nil, 0, err
}

// Generate the sort criteria at the end of the query clause 
if err := qppg.SortQuery(sortFields, qp.Sort, &queryClause, &queryParams); err != nil {
	return nil, 0, err
}

// Append limit and offset if specified
if qp.Limit > 0 {
	queryClause.WriteString(" LIMIT " + strconv.Itoa(qp.Limit))
}
if qp.Offset > 0 {
	queryClause.WriteString(" OFFSET " + strconv.Itoa(qp.Offset))
}

query, err := db.Query("SELECT * FROM table_name "+queryClause.String(), queryParams...)
if err != nil {
	log.Fatal(err)
}

Thanks

Special thanks to my employer https://geneticnetworks.com for letting me work on this and open source it.

Documentation

Index

• Constants
• Variables
• type Field
• type Filter
  • func (f Filter) Append(logic FilterLogic, field Field, op FilterOp, value interface{}) Filter
• type FilterField
• type FilterFieldCustom
  • func (ffc FilterFieldCustom) Name() string
  • func (ffc FilterFieldCustom) Type() FilterType
• type FilterFieldTypes
  • func (fft FilterFieldTypes) FindFilterType(search string) (string, FilterType)
• type FilterLogic
  • func (logic FilterLogic) MarshalJSON() ([]byte, error)
  • func (logic FilterLogic) String() string
• type FilterOp
  • func (op FilterOp) MarshalJSON() ([]byte, error)
  • func (op FilterOp) String() string
• type FilterTerm
• type FilterType
  • func (ft FilterType) Name() string
  • func (ft FilterType) Type() FilterType
• type Options
  • func (o Options) HasOption(option string) bool
  • func (o Options) Set(option string, value string) Options
• type QueryParameters
  • func ParseQuery(q string) (*QueryParameters, error)
  • func ParseRawQuery(rq string) (*QueryParameters, error)
  • func (qp *QueryParameters) PrettyString() string
  • func (qp *QueryParameters) Reset()
  • func (qp *QueryParameters) String() string
• type Sort
• type SortFields
• type SortTerm

Constants

const (
	FilterOpEquals FilterOp = iota
	FilterOpNotEquals

	FilterOpLessThan
	FilterOpLessThanEqual
	FilterOpGreaterThan
	FilterOpGreaterThanEqual

	FilterOpLike
	FilterOpNotLike
	FilterOpILike
	FilterOpNotILike

	FilterOpRegexp
	FilterOpNotRegexp
	FilterOpIRegexp
	FilterOpNotIRegexp

	FilterOpSymEquals      = "="
	FilterOpSymNotEquals   = "!="
	FilterOpSymLessThan    = "<"
	FilterOpSymGreaterThan = ">"

	FilterOpSymLessThanEqual     = "<="
	FilterOpSymLessThanEqual2    = "=<"
	FilterOpSymGreaterThanEqual  = ">="
	FilterOpSymGreaterThanEqual2 = "=>"

	FilterOpSymLike     = "=~"
	FilterOpSymNotLike  = "!=~"
	FilterOpSymILike    = "=~~"
	FilterOpSymNotILike = "!=~~"

	FilterOpSymRegexp     = ":"
	FilterOpSymNotRegexp  = "!:"
	FilterOpSymIRegexp    = ":~"
	FilterOpSymNotIRegexp = "!:~"
)

Variables

var (
	FilterOpSymToFilterOp = map[string]FilterOp{
		FilterOpSymEquals:            FilterOpEquals,
		FilterOpSymNotEquals:         FilterOpNotEquals,
		FilterOpSymLessThan:          FilterOpLessThan,
		FilterOpSymGreaterThan:       FilterOpGreaterThan,
		FilterOpSymLessThanEqual:     FilterOpLessThanEqual,
		FilterOpSymLessThanEqual2:    FilterOpLessThanEqual,
		FilterOpSymGreaterThanEqual:  FilterOpGreaterThanEqual,
		FilterOpSymGreaterThanEqual2: FilterOpGreaterThanEqual,
		FilterOpSymLike:              FilterOpLike,
		FilterOpSymNotLike:           FilterOpNotLike,
		FilterOpSymILike:             FilterOpILike,
		FilterOpSymNotILike:          FilterOpNotILike,
		FilterOpSymRegexp:            FilterOpRegexp,
		FilterOpSymNotRegexp:         FilterOpNotRegexp,
		FilterOpSymIRegexp:           FilterOpIRegexp,
		FilterOpSymNotIRegexp:        FilterOpNotIRegexp,
	}
	FilterOpToFilterOpSym = map[FilterOp]string{
		FilterOpEquals:           FilterOpSymEquals,
		FilterOpNotEquals:        FilterOpSymNotEquals,
		FilterOpLessThan:         FilterOpSymLessThan,
		FilterOpGreaterThan:      FilterOpSymGreaterThan,
		FilterOpLessThanEqual:    FilterOpSymLessThanEqual,
		FilterOpGreaterThanEqual: FilterOpSymGreaterThanEqual,
		FilterOpLike:             FilterOpSymLike,
		FilterOpNotLike:          FilterOpSymNotLike,
		FilterOpILike:            FilterOpSymILike,
		FilterOpNotILike:         FilterOpSymNotILike,
		FilterOpRegexp:           FilterOpSymRegexp,
		FilterOpNotRegexp:        FilterOpSymNotRegexp,
		FilterOpIRegexp:          FilterOpSymIRegexp,
		FilterOpNotIRegexp:       FilterOpSymNotIRegexp,
	}
)

var (
	ErrCouldNotParse = errors.New("could not parse")
)

Handles parsing query requests with complex matching and precedence

var (
	FilterLogicSymToFilterLogic = map[string]FilterLogic{
		FilterLogicSymAnd: FilterLogicAnd,
		FilterLogicSymOr:  FilterLogicOr,
	}
)

Types

type Field

type Field = string // Alias

type Filter

type Filter []FilterTerm

func (Filter) Append

func (f Filter) Append(logic FilterLogic, field Field, op FilterOp, value interface{}) Filter

type FilterField

type FilterField interface {
	Name() string
	Type() FilterType
}

type FilterFieldCustom

type FilterFieldCustom struct {
	FieldName  string
	FilterType FilterType
}

func (FilterFieldCustom) Name

func (ffc FilterFieldCustom) Name() string

func (FilterFieldCustom) Type

func (ffc FilterFieldCustom) Type() FilterType

type FilterFieldTypes

type FilterFieldTypes map[Field]FilterField

func (FilterFieldTypes) FindFilterType

func (fft FilterFieldTypes) FindFilterType(search string) (string, FilterType)

type FilterLogic

type FilterLogic int

const (
	FilterLogicStart FilterLogic = iota
	FilterLogicAnd
	FilterLogicOr

	FilterLogicSymAnd = "&"
	FilterLogicSymOr  = "|"
)

func (FilterLogic) MarshalJSON

func (logic FilterLogic) MarshalJSON() ([]byte, error)

func (FilterLogic) String

func (logic FilterLogic) String() string

type FilterOp

type FilterOp int

func (FilterOp) MarshalJSON

func (op FilterOp) MarshalJSON() ([]byte, error)

func (FilterOp) String

func (op FilterOp) String() string

type FilterTerm

type FilterTerm struct {
	Logic FilterLogic `json:"logic"`
	Op    FilterOp    `json:"op"`
	Field Field       `json:"field"`
	Value string      `json:"value"`

	SubFilter Filter `json:"sub_filter,omitempty"`
}

type FilterType

type FilterType int

const (
	FilterTypeNotFound FilterType = iota
	FilterTypeSimple
	FilterTypeString
	FilterTypeNumeric
	FilterTypeTime
	FilterTypeBool
)

func (FilterType) Name

func (ft FilterType) Name() string

func (FilterType) Type

func (ft FilterType) Type() FilterType

type Options

type Options map[string]string // Just a lookup of string

func (Options) HasOption

func (o Options) HasOption(option string) bool

func (Options) Set

func (o Options) Set(option string, value string) Options

type QueryParameters

type QueryParameters struct {
	Filter  Filter  `json:"filter"`
	Sort    Sort    `json:"sort"`
	Options Options `json:"options"`
	Limit   int     `json:"limit"`
	Offset  int     `json:"offset"`
}

func ParseQuery

func ParseQuery(q string) (*QueryParameters, error)

ParseQuery converts a string into query parameters This loosely follows standard HTTP URL encoding

func ParseRawQuery

func ParseRawQuery(rq string) (*QueryParameters, error)

func (*QueryParameters) PrettyString

func (qp *QueryParameters) PrettyString() string

func (*QueryParameters) Reset

func (qp *QueryParameters) Reset()

func (*QueryParameters) String

func (qp *QueryParameters) String() string

type Sort

type Sort []SortTerm

type SortFields

type SortFields []string

type SortTerm

type SortTerm struct {
	Field Field `json:"field"`
	Desc  bool  `json:"desc"`
}