QueryBuilder.go

// This is a helper function to make Go work with https://github.com/marmelab/admin-on-rest parameters 
// a little bit easier.

func QueryBuilder(tableName string, cols string, query url.Values) (string, error) {
	// only match string like `name`, `users.name`
	var columnRegexp = regexp.MustCompile("^[a-zA-Z\\d]+(\\.[a-zA-Z\\d]+)*$")

	baseQuery := fmt.Sprintf("SELECT %s FROM %s", cols, tableName)

	colsMap := make(map[string]bool)
	for _, c := range strings.Split(cols, ",") {
		if !columnRegexp.MatchString(c) {
			return "", errors.New("bad column name")
		}
		colsMap[c] = true
	}

	orderByClause := ""
	whereClause := ""
	limitClause := ""
	ascOrDesc := ""

	begin := -1
	end := -1

	var err error

	for param := range query {
		val := query.Get(param)
		switch param {
		case "_order":
			if strings.ToLower(val) == "asc" || strings.ToLower(val) == "desc" {
				ascOrDesc = val
			}
		case "_sort":
			if _, ok := colsMap[val]; ok {
				orderByClause = fmt.Sprintf("ORDER BY %s", val)
			}
		case "_start":
			if begin, err = strconv.Atoi(val); err != nil {
				begin = -1
				end = -1
			}
		case "_end":
			if end, err = strconv.Atoi(val); err != nil {
				begin = -1
				end = -1
			}
		default:
			if _, ok := colsMap[param]; ok {
				whereClause = fmt.Sprintf("WHERE %s = '%s'", param, val)
			}
		}
	}

	if len(orderByClause) > 0 {
		orderByClause += " " + ascOrDesc
	}

	if begin >= 0 && end >= begin {
		limitClause = fmt.Sprintf("LIMIT %d OFFSET %d", end-begin, begin)
	}

	return fmt.Sprintf("%s %s %s %s", baseQuery, whereClause, orderByClause, limitClause), nil
}

// Use it like:
query, err := util.QueryBuilder("users", "id,email,name", r.URL.Query())