Skip to content

Instantly share code, notes, and snippets.

@deoxxa
Last active November 18, 2016 03:24
Show Gist options
  • Save deoxxa/c83a2fd5a6d9d0bc1e07b84ad74efda7 to your computer and use it in GitHub Desktop.
Save deoxxa/c83a2fd5a6d9d0bc1e07b84ad74efda7 to your computer and use it in GitHub Desktop.
// ...
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()
// ...
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment