pm/pkg/db/query.go

package db

import (
	"fmt"
	"strings"
)

type Join struct {
	TableName string
	As        string
	On        []string
}

type Select struct {
	TableName string
	Select    map[string]string
	InnerJoin []Join
	LeftJoin  []Join
	Where     []string
	GroupBy   string
	Having    []string
	OrderBy   string
	Limit     int64
	Offset    int64
	params    map[string]interface{}
}

func (s *Select) Param(value interface{}) string {
	if s.params == nil {
		s.params = make(map[string]interface{})
	}
	name := fmt.Sprintf("p%d", len(s.params))
	s.params[name] = value
	return fmt.Sprintf("{{%s}}", name)
}

func (s Select) Query() (string, map[string]interface{}) {
	query := strings.Builder{}
	query.WriteString("SELECT ")
	if len(s.Select) == 0 {
		query.WriteString("*")
	} else {
		dot := ""
		for k, v := range s.Select {
			query.WriteString(fmt.Sprintf("%s%s AS `%s`", dot, v, k))
			dot = ", "
		}
	}
	query.WriteString(" FROM ")
	if s.TableName[0] != '`' && s.TableName[len(s.TableName)-1] != '`' && !strings.Contains(s.TableName, " ") {
		query.WriteString(fmt.Sprintf("`%s`", s.TableName))
	} else {
		query.WriteString(s.TableName)
	}

	for _, join := range []struct {
		Type string
		Data []Join
	}{{"INNER JOIN", s.InnerJoin}, {"LEFT JOIN", s.LeftJoin}} {
		for _, v := range join.Data {
			as := ""
			if v.As != "" {
				as = fmt.Sprintf("AS `%s`", v.As)
			}
			table := v.TableName
			if !strings.Contains(table, " ") {
				table = fmt.Sprintf("`%s`", table)
			}
			query.WriteString(fmt.Sprintf(" %s %s %s ON %s", join.Type, table, as, strings.Join(v.On, " AND ")))
		}
	}

	if len(s.Where) != 0 {
		query.WriteString(" WHERE (")
		query.WriteString(strings.Join(s.Where, ") AND ("))
		query.WriteString(")")
	}

	if s.GroupBy != "" {
		query.WriteString(" GROUP BY ")
		query.WriteString(s.GroupBy)
	}

	if len(s.Having) != 0 {
		query.WriteString(" HAVING ")
		query.WriteString(strings.Join(s.Having, " AND "))
	}

	if s.OrderBy != "" {
		query.WriteString(" ORDER BY ")
		query.WriteString(s.OrderBy)
	}

	if s.Limit != 0 {
		query.WriteString(fmt.Sprintf(" LIMIT %d", s.Limit))
		if s.Offset != 0 {
			query.WriteString(fmt.Sprintf(" OFFSET %d", s.Offset))
		}
	}

	if s.params == nil {
		s.params = make(map[string]interface{})
	}
	return query.String(), s.params
}