Golang squirrel.SelectBuilder类代码示例

// ApplyTo returns a new SelectBuilder after applying the paging effects of
// `p` to `sql`.  This method provides the default case for paging: int64
// cursor-based paging by an id column.
func (p PageQuery) ApplyTo(
	sql sq.SelectBuilder,
	col string,
) (sq.SelectBuilder, error) {
	sql = sql.Limit(p.Limit)

	cursor, err := p.CursorInt64()
	if err != nil {
		return sql, err
	}

	switch p.Order {
	case "asc":
		sql = sql.
			Where(fmt.Sprintf("%s > ?", col), cursor).
			OrderBy(fmt.Sprintf("%s asc", col))
	case "desc":
		sql = sql.
			Where(fmt.Sprintf("%s < ?", col), cursor).
			OrderBy(fmt.Sprintf("%s desc", col))
	default:
		return sql, errors.Errorf("invalid order: %s", p.Order)
	}

	return sql, nil
}

func addFilterToQuery(s *schema.Schema, q sq.SelectBuilder, filter map[string]interface{}, join bool) sq.SelectBuilder {
	if filter == nil {
		return q
	}
	for key, value := range filter {
		property, err := s.GetPropertyByID(key)
		var column string
		if join {
			column = makeColumn(s, *property)
		} else {
			column = quote(key)
		}
		if err != nil {
			log.Notice(err.Error())
			continue
		}

		if property.Type == "boolean" {
			v := make([]bool, len(value.([]string)))
			for i, j := range value.([]string) {
				v[i], _ = strconv.ParseBool(j)
			}
			q = q.Where(sq.Eq{column: v})
		} else {
			q = q.Where(sq.Eq{column: value})
		}
	}
	return q
}

func addFilterToQuery(s *schema.Schema, q sq.SelectBuilder, filter map[string]interface{}, join bool) (sq.SelectBuilder, error) {
	if filter == nil {
		return q, nil
	}
	for key, value := range filter {
		property, err := s.GetPropertyByID(key)

		if err != nil {
			return q, err
		}

		var column string
		if join {
			column = makeColumn(s.GetDbTableName(), *property)
		} else {
			column = quote(key)
		}

		queryValues, ok := value.([]string)
		if ok && property.Type == "boolean" {
			v := make([]bool, len(queryValues))
			for i, j := range queryValues {
				v[i], _ = strconv.ParseBool(j)
			}
			q = q.Where(sq.Eq{column: v})
		} else {
			q = q.Where(sq.Eq{column: value})
		}
	}
	return q, nil
}

func SelectOne(s gorp.SqlExecutor, builder squirrel.SelectBuilder, src interface{}) error {
	sql, args, err := builder.ToSql()
	if err != nil {
		return err
	}
	return s.SelectOne(src, sql, args...)
}

// Get gets a single row returned by the provided sql builder into the provided dest.
// dest must be a non-slice value of the correct record type.
func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return errors.Wrap(err, 1)
	}

	return q.GetRaw(ctx, query, args, dest)
}

func (q SqlQuery) Query(ctx context.Context, sql sq.SelectBuilder) (*sqlx.Rows, error) {
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return nil, errors.Wrap(err, 1)
	}

	return q.QueryRaw(ctx, query, args)
}

func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return err
	}
	return db.Get(dest, query, args...)
}

func (q SqlQuery) Select(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return err
	}

	log.WithField(ctx, "sql", query).Info("Executing query")

	return db.Select(dest, query, args...)
}

func makeJoin(s *schema.Schema, q sq.SelectBuilder) sq.SelectBuilder {
	manager := schema.GetManager()
	for _, property := range s.Properties {
		if property.RelationProperty == "" {
			continue
		}
		relatedSchema, _ := manager.Schema(property.Relation)
		q = q.LeftJoin(
			quote(relatedSchema.GetDbTableName()) + fmt.Sprintf(" on %s.%s = %s.id", s.GetDbTableName(), property.ID, relatedSchema.GetDbTableName()))
		q = makeJoin(relatedSchema, q)
	}
	return q
}

// loadValue uses QueryBuilder to run SELECT query and fetch single value from database,
// use it for getting ID or counter
func LoadValue(qb *squirrel.SelectBuilder, buf interface{}) error {
	query, args, err := qb.ToSql()

	if err != nil {
		return err
	}

	err = components.App.Db.Unsafe().QueryRow(query, args...).Scan(buf)
	if err != nil && err != sql.ErrNoRows {
		return err
	}

	return nil
}

func LoadStruct(qb *squirrel.SelectBuilder, buf interface{}) error {

	query, args, err := qb.ToSql()

	if err != nil {
		return err
	}

	err = components.App.Db.Unsafe().Get(buf, query, args...)
	if err != nil {
		return err
	}

	return nil
}

func makeJoin(s *schema.Schema, tableName string, q sq.SelectBuilder) sq.SelectBuilder {
	manager := schema.GetManager()
	for _, property := range s.Properties {
		if property.RelationProperty == "" {
			continue
		}
		relatedSchema, _ := manager.Schema(property.Relation)
		aliasTableName := makeAliasTableName(tableName, property)
		q = q.LeftJoin(
			fmt.Sprintf("%s as %s on %s.%s = %s.id", quote(relatedSchema.GetDbTableName()), quote(aliasTableName),
				quote(tableName), quote(property.ID), quote(aliasTableName)))
		q = makeJoin(relatedSchema, aliasTableName, q)
	}
	return q
}

func (f *EffectLedgerFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
	start := TotalOrderID{LedgerSequence: f.LedgerSequence}
	end := TotalOrderID{LedgerSequence: f.LedgerSequence + 1}
	return sql.Where(
		"(heff.history_operation_id >= ? AND heff.history_operation_id < ?)",
		start.ToInt64(),
		end.ToInt64(),
	), nil
}

func whereValueForKey(builder squirrel.SelectBuilder, src interface{}, key, value string) (squirrel.SelectBuilder, error) {
	keyParts := strings.Split(key, "-")
	sqlString := ""

	switch len(keyParts) {
	case 1:
		if !structHasField(src, keyParts[0]) {
			return builder, nil
		}
		sqlString = keyParts[0] + " = ?"
	case 2:
		op := Operator(keyParts[0])
		field := keyParts[1]
		if !structHasField(src, field) {
			return builder, nil
		}
		switch op {
		case Lt:
			sqlString = field + " < ?"
		case Lte:
			sqlString = field + " <= ?"
		case Gt:
			sqlString = field + " > ?"
		case Gte:
			sqlString = field + " >= ?"
		case In:
			values := strings.Split(value, ",")
			builder = builder.Where(squirrel.Eq{field: values})
			return builder, nil
		default:
			return builder, nil
		}
	default:
		return builder, nil
	}

	builder = builder.Where(sqlString, value)
	return builder, nil
}