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
}