Is there a better way to build query?

[Codebreaker101]

Currently I'm making a query by using this function.

type EqualityFilter struct {
	Key   string
	Value string
}
type IncludesFilter struct {
	Key    string
	Values []string
}
type AndFilter struct {
	Filters []Filter
}
type OrFilter struct {
	Filters []Filter
}

// appendFilters(q, details.Filters, "AND", q.Where)

func appendFilters(q *bun.SelectQuery, filters []rapid.Filter, sep string, where func(query string, args ...interface{}) *bun.SelectQuery) (*bun.SelectQuery, error) {
	for i := range filters {
		switch filter := filters[i].(type) {
		case *rapid.EqualityFilter:
			q = where(fmt.Sprintf("%s = ?", translateFieldToOperator(filter.Key, true)), filter.Value)
		case *rapid.IncludesFilter:
			q = where(fmt.Sprintf("%s IN (?)", translateFieldToOperator(filter.Key, true)), bun.In(filter.Values))
		case *rapid.OrFilter:
			q = q.WhereGroup(fmt.Sprintf(" %s ", sep), func(*bun.SelectQuery) *bun.SelectQuery {
				var err error
				q, err = appendFilters(q, filter.Filters, "OR", q.WhereOr)
				if err != nil {
					panic(fmt.Errorf("unable to construct query"))
				}
				return q
			})
		case *rapid.AndFilter:
			q = q.WhereGroup(fmt.Sprintf(" %s ", sep), func(*bun.SelectQuery) *bun.SelectQuery {
				var err error
				q, err = appendFilters(q, filter.Filters, "AND", q.Where)
				if err != nil {
					panic(fmt.Errorf("unable to construct query"))
				}
				return q
			})
		default:
			return nil, fmt.Errorf("unknown filter %T", filter)
		}
	}

	return q, nil
}

func translateFieldToOperator(field string, asText bool) string {
	field = "value." + field

	fieldParts := strings.Split(field, ".")
	filterField := fieldParts[0]
	fieldParts = fieldParts[1:]

	for fieldIndex, fieldPart := range fieldParts {
		filterField += "->"

		if asText && fieldIndex+1 == len(fieldParts) {
			filterField += ">"
		}

		filterField += fmt.Sprintf("'%s'", fieldPart)
	}

	return filterField
}

It's not pretty but it works. I'm wondering is there a better way to construct the query that I am missing?

[vmihailenco]

I'd say it is as pretty as it can be. I would only replace fmt.Sprintf with something like db.Where("? ? ?", bun.Indent("col", bun.Safe("="), "value")).

You can also check query generation at https://github.com/uptrace/uptrace/blob/master/pkg/tracing/span_filter.go#L196-L255

[Codebreaker101]

bun.Ident does not seem to be working for jsonb operators. It generates query with the operator in quotation marks:

q = where("? ? ?", bun.Ident(translateFieldToOperator(filter.Key, true)), bun.Safe("="), filter.Value)

SELECT * FROM "state"  WHERE (("value->>'Age'" = '6') OR ((("value->>'Age'" = '7') AND ("value->>'Year'" = '2001')) OR (("value->>'Age'" = '7') AND ("value->>'Year'" = '2000')))) ORDER BY value->'Age' ASC, value->'Name' DESC

[vmihailenco]

Yes, you would need to refactor the generator so you can write something like q.Where("? -> ? = ?", ...)