func (this *QueryBuilder) Group(query *db.Query) {
groups := query.GetGroupByTokens()
for _, group := range groups {
//this.groupPart.Add(this.translator.ColumnAlias(group.Token, group.Position))
this.groupPart.Add(this.translator.Translate(db.QUERY, group.Token))
}
}
func (this *QueryBuilder) FromSubQuery(query *db.Query) {
subquery := query.GetSubQuery()
alias := query.GetSubQueryAlias()
this.fromPart.AddAsOne("(", this.translator.GetSqlForQuery(subquery), ")")
if alias != "" {
this.fromPart.Append(" ", alias)
}
}
func (this *QueryBuilder) Union(query *db.Query) {
unions := query.GetUnions()
for _, u := range unions {
this.unionPart.Add(" UNION ")
if u.All {
this.unionPart.Add("ALL ")
}
this.unionPart.Add(this.translator.GetSqlForQuery(u.Query))
}
}
func (this *QueryBuilder) Order(query *db.Query) {
orders := query.GetOrders()
for _, ord := range orders {
if ord.GetHolder() != nil {
this.orderPart.Add(this.translator.Translate(db.QUERY, ord.GetHolder()))
} else {
this.orderPart.Add(ord.GetAlias())
}
if ord.IsAsc() {
this.orderPart.Append(" ASC")
} else {
this.orderPart.Append(" DESC")
}
}
}
func (this *GenericTranslator) CreateQueryProcessor(query *db.Query) QueryProcessor {
proc := this.QueryProcessorFactory()
proc.Column(query)
if query.GetTable() != nil {
proc.From(query)
} else {
proc.FromSubQuery(query)
}
proc.Where(query)
// it is after the where clause because the joins can go to the where clause,
// and this way the restrictions over the driving table will be applied first
AppendJoins(query.GetJoins(), proc)
proc.Group(query)
proc.Having(query)
proc.Union(query)
proc.Order(query)
return proc
}
func (this *GenericTranslator) GetSqlForQuery(query *db.Query) string {
proc := this.CreateQueryProcessor(query)
// SELECT COLUNAS
sel := tk.NewStrBuffer()
sel.Add("SELECT ")
if query.IsDistinct() {
sel.Add("DISTINCT ")
}
sel.Add(proc.ColumnPart())
// FROM
sel.Add(" FROM ", proc.FromPart())
// JOINS
sel.Add(proc.JoinPart())
// WHERE - conditions
if query.GetCriteria() != nil {
sel.Add(" WHERE ", proc.WherePart())
}
// GROUP BY
if len(query.GetGroupBy()) != 0 {
sel.Add(" GROUP BY ", proc.GroupPart())
}
// HAVING
if query.GetHaving() != nil {
sel.Add(" HAVING ", proc.HavingPart())
}
// UNION
if len(query.GetUnions()) != 0 {
sel.Add(proc.UnionPart())
}
// ORDER
if len(query.GetOrders()) != 0 {
sel.Add(" ORDER BY ", proc.OrderPart())
}
sql := this.overrider.PaginateSQL(query, sel.String())
return sql
}
func (this *QueryBuilder) Having(query *db.Query) {
having := query.GetHaving()
if having != nil {
this.havingPart.Add(this.translator.Translate(db.QUERY, having))
}
}
func (this *QueryBuilder) Where(query *db.Query) {
criteria := query.GetCriteria()
if criteria != nil {
this.wherePart.Add(this.translator.Translate(db.QUERY, criteria))
}
}
func (this *QueryBuilder) From(query *db.Query) {
table := query.GetTable()
alias := query.GetTableAlias()
this.fromPart.AddAsOne(this.translator.TableName(table), " ", alias)
}
func (this *FirebirdSQLTranslator) PaginateSQL(query *db.Query, sql string) string {
sb := tk.NewStrBuffer()
if query.GetLimit() > 0 {
sb.Add(sql, " ROWS ")
if query.GetSkip() > 0 {
sb.Add(":", db.OFFSET_PARAM, " TO ")
query.SetParameter(db.OFFSET_PARAM, query.GetSkip()+1)
}
sb.Add(":", db.LIMIT_PARAM)
query.SetParameter(db.LIMIT_PARAM, query.GetSkip()+query.GetLimit())
return sb.String()
}
return sql
}
func (this *MySQL5Translator) PaginateSQL(query *db.Query, sql string) string {
sb := tk.NewStrBuffer()
if query.GetLimit() > 0 {
sb.Add(sql, " LIMIT :", db.OFFSET_PARAM, ", :", db.LIMIT_PARAM)
if query.GetSkip() >= 0 {
query.SetParameter(db.OFFSET_PARAM, query.GetSkip())
}
query.SetParameter(db.LIMIT_PARAM, query.GetLimit())
return sb.String()
}
return sql
}
func (this *OracleTranslator) PaginateSQL(query *db.Query, sql string) string {
if query.GetSkip() > 0 {
query.SetParameter(db.OFFSET_PARAM, query.GetSkip()+1)
query.SetParameter(db.LIMIT_PARAM, query.GetSkip()+query.GetLimit())
return fmt.Sprintf("select * from ( select a.*, rownum rnum from ( %s ) a where rownum <= :%s ) where rnum >= :%s",
sql, db.LIMIT_PARAM, db.OFFSET_PARAM)
} else if query.GetLimit() > 0 {
query.SetParameter(db.LIMIT_PARAM, query.GetLimit())
return fmt.Sprintf("select * from ( %s ) where rownum <= :%s", sql, db.LIMIT_PARAM)
}
return sql
}
func QueryForPage(
query *db.Query,
criteria Criteria,
target interface{},
transformer func(in interface{}) interface{},
) (Page, error) {
max := criteria.PageSize
first := (criteria.Page - 1) * max
// for the first page the offset is zero
query.Skip(first)
if max > 0 {
query.Limit(max + 1)
}
var entities coll.Collection
var err error
var results []interface{}
if reflect.TypeOf(target).Kind() == reflect.Func {
results, err = query.ListInto(target)
} else if _, ok := target.(tk.Hasher); ok {
entities, err = query.ListFlatTreeOf(target)
} else {
entities, err = query.ListOf(target)
}
if err != nil {
return Page{}, err
}
if results == nil {
results = entities.Elements()
}
page := Page{}
size := int64(len(results))
if max > 0 && size > max {
page.Last = false
page.Results = results[:max]
} else {
page.Last = true
page.Results = results
}
// transform results
if transformer != nil {
for k, v := range page.Results {
page.Results[k] = transformer(v)
}
}
// count records
if criteria.CountRecords {
DB := query.GetDb()
cnt := DB.Query(query.GetTable())
cnt.Copy(query)
cnt.ColumnsReset()
cnt.CountAll()
cnt.OrdersReset()
var recs int64
_, err = cnt.SelectInto(&recs)
if err != nil {
return Page{}, err
}
page.Count = Int64(recs)
}
return page, nil
}