Last active
November 18, 2016 03:24
-
-
Save deoxxa/c83a2fd5a6d9d0bc1e07b84ad74efda7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// ... | |
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() | |
// ... |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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