example.go

// ...

sqb := newBuilder(6)
cqb := newBuilder(6)

b := []*builder{sqb, cqb}

sqb.push(0, "select prd.id, prd.name, prd.picture, (prc.price * 100)::numeric::integer, cg.date")
cqb.push(0, "select count(distinct prd.id)")
pushAll(b, 1, "from stores s")
pushAll(b, 2, "join catalogues cg on cg.id = s.catalogue_id")
pushAll(b, 2, "join prices prc on prc.catalogue_id = cg.id")
pushAll(b, 2, "join products prd on prd.id = prc.product_id")
pushAll(b, 3, "where s.id = ?", storeID)

if s := r.URL.Query().Get("categoryId"); s != "" {
  if id, err := uuid.FromString(s); err != nil {
    panic(err)
  } else {
    pushAll(b, 2, `join prices_categories pc on pc.price_id = prc.id`)
    pushAll(b, 3, `and pc.category_id = ?`, id)
  }
}

if s := r.URL.Query().Get("name"); s != "" {
  pushAll(b, 3, `and prd.name @@ ?`, s)
}

switch r.URL.Query().Get("order") {
case "name":
  sqb.push(4, `order by prd.name asc`)
case "-name":
  sqb.push(4, `order by prd.name desc`)
case "price":
  sqb.push(4, `order by prc.price asc`)
case "-price":
  sqb.push(4, `order by prc.price desc`)
default:
  if s := r.URL.Query().Get("name"); s != "" {
    sqb.push(4, "order by similarity(prd.name, ?) desc", s)
  } else {
    sqb.push(4, "order by prd.name asc")
  }
}

if s := r.URL.Query().Get("ids"); s != "" {
  a := strings.Split(s, ",")

  pushAll(b, 3, `and prd.id in (`)

  for i, idString := range a {
    id, err := uuid.FromString(idString)
    if err != nil {
      panic(err)
    }

    pushAll(b, 3, `?`, id)

    if i != len(a)-1 {
      pushAll(b, 3, `,`)
    }
  }

  pushAll(b, 3, `)`)
} else {
  sqb.push(5, `offset ? limit ?`, offset, limit)
}

cq, cv := cqb.build()

var total int
if err := c.db.QueryRow(r.Context(), cq, cv...).Scan(&total); err != nil {
  panic(err)
}

sq, sv := sqb.build()

productRows, err := c.db.Query(r.Context(), sq, sv...)
if err != nil {
  panic(err)
}
defer productRows.Close()

// ...

querybuilder.go

package main

import (
	"fmt"
	"regexp"
)

type sqlQuery string

type queryBit struct {
	q string
	v []interface{}
}

type builder struct {
	a [][]queryBit
}

func newBuilder(n int) *builder {
	return &builder{a: make([][]queryBit, n)}
}

func pushAll(a []*builder, n int, q string, v ...interface{}) {
	for _, b := range a {
		b.push(n, q, v...)
	}
}

func (b *builder) push(n int, q string, v ...interface{}) {
	b.a[n] = append(b.a[n], queryBit{regexp.MustCompile(`\s+`).ReplaceAllString(q, " "), v})
}

func (b *builder) build() (sqlQuery, []interface{}) {
	var q string
	var v []interface{}

	i := 0

	for _, l := range b.a {
		for _, e := range l {
			q += regexp.MustCompile(`\?`).ReplaceAllStringFunc(e.q, func(s string) string {
				i++
				return fmt.Sprintf("$%d", i)
			}) + " "

			v = append(v, e.v...)
		}
	}

	return sqlQuery(q), v
}

func (b *builder) print() string {
	var q string

	for _, l := range b.a {
		for _, e := range l {
			i := 0
			q += regexp.MustCompile(`\?`).ReplaceAllStringFunc(e.q, func(s string) string {
				p := fmt.Sprintf("'%s'", e.v[i])
				i++
				return p
			}) + " "
		}
	}

	return q
}