uni/WEB43-diary/internal/database/queryBuilder.go

package database

import (
	"fmt"
	"reflect"
	"strings"
)

type JoinType string
type QueryKind string

const (
	Join        JoinType = "JOIN"
	NaturalJoin JoinType = "NATURAL JOIN"
	InnerJoin   JoinType = "INNER JOIN"
	LeftJoin    JoinType = "LEFT JOIN"
	RightJoin   JoinType = "RIGHT JOIN"

	SelectQuery QueryKind = "SELECT"
	DeleteQuery QueryKind = "DELETE"
)

type JoinCondition struct {
	JoinType JoinType
	Table    string
	On       string
}

type queryBuilder[Obj SQLTable] struct {
	object       Obj
	table        *sqlTable
	selectedRows []string
	distinct     bool
	highPriority bool
	joins        []*JoinCondition
	where        WhereGroups
	havingStr    string
	havingValues []any
	groupBy      []string
	orderBy      []string
	limit        int
	offset       int
}

func NewQueryBuilder[Val SQLTable](obj Val) *queryBuilder[Val] {
	return &queryBuilder[Val]{object: obj, table: getSQLTableFromInterface(obj)}
}

func QueryBuilderFromInterface[Val SQLTable](obj Val) *queryBuilder[Val] {
	builder := queryBuilder[Val]{object: obj, table: getSQLTableFromInterface(obj)}

	reflectElem := reflect.ValueOf(obj).Elem()
	reflectType := reflectElem.Type()
	for i := 0; i < reflectElem.NumField(); i++ {
		field := reflectElem.Field(i)
		if len(reflectType.Field(i).Tag.Get("db")) < 2 || !field.IsValid() || field.IsZero() {
			continue
		}

		builder.Where(&QueryCondition{Row: reflectType.Field(i).Name, Operator: Equal, Value: field.Interface()})
	}

	return &builder
}

func (builder *queryBuilder[TVal]) Select(rows ...string) *queryBuilder[TVal] {
	if len(rows) > 0 {
		builder.selectedRows = append(builder.selectedRows, rows...)
	}
	return builder
}

func (builder *queryBuilder[TVal]) Distinct() *queryBuilder[TVal] {
	builder.distinct = true
	return builder
}

func (builder *queryBuilder[TVal]) HighPriority() *queryBuilder[TVal] {
	builder.highPriority = true
	return builder
}

func (builder *queryBuilder[TVal]) Join(joinType JoinType, table SQLTable, on string) *queryBuilder[TVal] {
	builder.joins = append(builder.joins, &JoinCondition{JoinType: joinType, Table: getSQLTableName(table), On: on})
	return builder
}

func (builder *queryBuilder[TVal]) Where(conditions ...*QueryCondition) *queryBuilder[TVal] {
	if len(conditions) > 0 {
		builder.where = append(builder.where, &WhereGroup{conditions, AND, nil})
	}
	return builder
}

func (builder *queryBuilder[TVal]) WhereGroup(groups ...*WhereGroup) *queryBuilder[TVal] {
	if len(groups) > 0 {
		builder.where = append(builder.where, groups...)
	}
	return builder
}

func (builder *queryBuilder[TVal]) Having(str string, values ...any) *queryBuilder[TVal] {
	builder.havingStr = str
	builder.havingValues = values
	return builder
}

func (builder *queryBuilder[TVal]) GroupBy(columns ...string) *queryBuilder[TVal] {
	if len(columns) > 0 {
		builder.groupBy = append(builder.groupBy, columns...)
	}
	return builder
}

func (builder *queryBuilder[TVal]) OrderBy(columns ...string) *queryBuilder[TVal] {
	if len(columns) > 0 {
		builder.orderBy = append(builder.orderBy, columns...)
	}
	return builder
}

func (builder *queryBuilder[TVal]) Limit(limit int) *queryBuilder[TVal] {
	builder.limit = limit
	return builder
}

func (builder *queryBuilder[TVal]) Offset(offset int) *queryBuilder[TVal] {
	builder.offset = offset
	return builder
}

func (builder *queryBuilder[TVal]) BuildSelect() (string, []any) {
	return builder.build(SelectQuery)
}

func (builder *queryBuilder[TVal]) BuildDelete() (string, []any) {
	return builder.build(DeleteQuery)
}

func (builder *queryBuilder[TVal]) build(queryKind QueryKind) (string, []any) {
	var query strings.Builder

	query.WriteString(fmt.Sprintf("%s ", queryKind))
	if queryKind == SelectQuery {
		if builder.distinct {
			query.WriteString("DISTINCT ")
		}
		if builder.highPriority {
			query.WriteString("HIGH_PRIORITY ")
		}
		if len(builder.selectedRows) > 0 {
			query.WriteString(strings.Join(builder.selectedRows, ", "))
		} else {
			query.WriteString("*")
		}
	}

	// FROM clause
	query.WriteString(" FROM ")
	query.WriteString(builder.table.Name)

	// JOIN clauses
	for _, join := range builder.joins {
		if join.On == "" {
			query.WriteString(fmt.Sprintf(" %s %s", join.JoinType, join.Table))
		} else {
			query.WriteString(fmt.Sprintf(" %s %s ON %s", join.JoinType, join.Table, join.On))
		}
	}

	// WHERE clause
	whereStatement, queryValues := builder.where.Build(true)
	if len(queryValues) > 0 {
		query.WriteString(whereStatement)
	}

	// GROUP BY clause
	if len(builder.groupBy) > 0 {
		query.WriteString(" GROUP BY ")
		query.WriteString(strings.Join(builder.groupBy, ", "))
	}

	// ORDER BY clause
	if len(builder.orderBy) > 0 {
		query.WriteString(" ORDER BY ")
		query.WriteString(strings.Join(builder.orderBy, ", "))
	}

	// HAVING clause
	if builder.havingStr != "" {
		query.WriteString(" HAVING ")
		query.WriteString(builder.havingStr)
		queryValues = append(queryValues, builder.havingValues...)
	}

	// LIMIT clause
	if builder.limit > 0 {
		query.WriteString(fmt.Sprintf(" LIMIT %d", builder.limit))
	}

	// OFFSET clause
	if builder.offset > 0 {
		query.WriteString(fmt.Sprintf(" OFFSET %d", builder.offset))
	}

	return query.String(), queryValues
}