Golang squirrel.Select函數代碼示例

// Get executes the query, returning any found results
func (q LedgerStateQuery) Select(ctx context.Context, dest interface{}) error {
	hSql := sq.
		Select("MAX(sequence) as horizonsequence").
		From("history_ledgers")

	scSql := sq.
		Select("MAX(ledgerseq) as stellarcoresequence").
		From("ledgerheaders")

	var result LedgerState

	err := q.Horizon.Get(ctx, hSql, &result)

	if err != nil {
		return err
	}

	err = q.Core.Get(ctx, scSql, &result)

	if err != nil {
		return err
	}

	setOn([]LedgerState{result}, dest)
	return nil
}

func Query(src interface{}, tableName string, values url.Values) (sql string, args []interface{}, err error) {
	builder := squirrel.Select("*").From(tableName)

	// add where clause
	for key, value := range values {
		nBuilder, err := whereValueForKey(builder, src, key, value[0])
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = nBuilder
	}

	// add order if it exists
	if oVal := values.Get(KeyOrder); oVal != "" {
		field, order, err := orderFromValue(src, oVal)
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = builder.OrderBy(field + " " + string(order))
	}

	// add limit and offset
	builder = builder.Limit(uintFromKey(values, KeyLimit, 1000))
	builder = builder.Offset(uintFromKey(values, KeyOffset, 0))

	return builder.ToSql()
}

func SelectAllEquipmentInfo(dbm *gorp.DbMap) ([]EquipmentInfo, error) {
	var results []EquipmentInfo

	q := sq.Select(
		`"EquipmentInfo"."ID"`,
		`"EquipmentInfo"."CertifiedName"`,
		`"RadioAccessTechnology"."Description" AS EquipmentType`,
		`"EquipmentInfo"."Model"`,
		`"EquipmentInfo"."AuthNumber"`,
		`"EquipmentInfo"."RadioType"`,
		`"EquipmentInfo"."IsApplied1421"`,
		`"EquipmentInfo"."AuthDate"`,
		`"EquipmentInfo"."Note"`,
		`"EquipmentInfo"."File"`,
	).From(
		"EquipmentInfo",
	).LeftJoin(
		`"RadioAccessTechnology" ON "EquipmentInfo"."EquipmentType" = "RadioAccessTechnology"."EquipmentType"`,
	)

	sql, _, err := q.ToSql()
	if err != nil {
		return results, err
	}

	if _, err := dbm.Select(&results, sql); err != nil {
		return nil, err
	}

	return results, nil
}

func (p *Place) Read(tx *sql.Tx) error {
	row := sq.Select("*").Where(sq.Eq{"id": p.ID}).RunWith(tx).QueryRow()
	if err := row.Scan(p.Row()...); err != nil {
		return err
	}
	return nil
}

func BenchmarkSquirrelBuilderComplex(b *testing.B) {
	arg_eq1 := squirrel.Eq{"f": 2, "x": "hi"}
	arg_eq2 := map[string]interface{}{"g": 3}
	arg_eq3 := squirrel.Eq{"h": []int{1, 2, 3}}

	b.ResetTimer()
	for n := 0; n < b.N; n++ {
		squirrel.Select("a", "b", "z", "y", "x").
			Distinct().
			From("c").
			Where("d = ? OR e = ?", 1, "wat").
			Where(arg_eq1).
			Where(arg_eq2).
			Where(arg_eq3).
			GroupBy("i").
			GroupBy("ii").
			GroupBy("iii").
			Having("j = k").
			Having("jj = ?", 1).
			Having("jjj = ?", 2).
			OrderBy("l").
			OrderBy("l").
			OrderBy("l").
			Limit(7).
			Offset(8).
			ToSql()
	}
}

//StateFetch fetches the state of the specified resource
func (tx *Transaction) StateFetch(s *schema.Schema, filter transaction.Filter) (state transaction.ResourceState, err error) {
	if !s.StateVersioning() {
		err = fmt.Errorf("Schema %s does not support state versioning.", s.ID)
		return
	}
	cols := makeStateColumns(s)
	q := sq.Select(cols...).From(quote(s.GetDbTableName()))
	q, _ = addFilterToQuery(s, q, filter, true)
	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	logQuery(sql, args...)
	rows, err := tx.transaction.Queryx(sql, args...)
	if err != nil {
		return
	}
	defer rows.Close()
	if !rows.Next() {
		err = fmt.Errorf("No resource found")
		return
	}
	data := map[string]interface{}{}
	rows.MapScan(data)
	err = decodeState(data, &state)
	return
}

func QueryQuestions(db *sqlx.DB, authorId int, query string, offset int) ([]Question, error) {
	qs := []Question{}
	qry := sq.Select("*").From("questions")

	if authorId != 0 {
		qry = qry.Where("author_id = ?", authorId)
	}
	if query != "" {
		word := fmt.Sprint("%", query, "%")
		qry = qry.Where("(title LIKE ? OR question LIKE ?)", word, word)
	}
	if offset > 0 {
		qry = qry.Offset(uint64(offset))
	}

	qry = qry.OrderBy("created_at DESC")
	qry = qry.PlaceholderFormat(sq.Dollar)
	sql, params, err := qry.ToSql()

	if err != nil {
		return qs, err
	} else {
		err := db.Select(&qs, sql, params...)
		dbErr := dbError(err)
		return qs, dbErr
	}
}

// TransactionsByLedger is a query that loads all rows from `txhistory` where
// ledgerseq matches `Sequence.`
func (q *Q) TransactionsByLedger(dest interface{}, seq int32) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		OrderBy("ctxh.txindex ASC").
		Where("ctxh.ledgerseq = ?", seq)

	return q.Select(dest, sql)
}

func (q SequenceByAddressQuery) Select(ctx context.Context, dest interface{}) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": q.Addresses})

	return q.SqlQuery.Select(ctx, sql, dest)
}

// LedgerHeaderBySequence is a query that loads a single row from the
// `ledgerheaders` table.
func (q *Q) LedgerHeaderBySequence(dest interface{}, seq int32) error {
	sql := sq.Select("clh.*").
		From("ledgerheaders clh").
		Limit(1).
		Where("clh.ledgerseq = ?", seq)

	return q.Get(dest, sql)
}

func (r *DaoResource) builderApplication() *sq.SelectBuilder {
	builder := sq.Select(
		"id", "token", "provider_id", "platform", "provider",
		"name", "attributes", "created_at", "updated_at").
		From(model.TableApplications)

	return &builder
}

// SequencesForAddresses loads the current sequence number for every accountid
// specified in `addys`
func (q *Q) SequencesForAddresses(dest interface{}, addys []string) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": addys})

	return q.Select(dest, sql)
}

// TransactionByHash is a query that loads a single row from the `txhistory`.
func (q *Q) TransactionByHash(dest interface{}, hash string) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		Limit(1).
		Where("ctxh.txid = ?", hash)

	return q.Get(dest, sql)
}

func ValidateToken(s gorp.SqlExecutor, memberID int64, token string) error {
	query := squirrel.Select("*").From(TableNameToken).
		Where(squirrel.Eq{"MemberID": memberID, "Value": token})
	tokens := []*Token{}
	sqlutil.Select(s, query, &tokens)
	if len(tokens) == 0 {
		return fmt.Errorf("token not found")
	}
	return nil
}

func GetID(dbmap *gorp.DbMap, m CrudResource, id interface{}) error {
	query := squirrel.Select("*").
		From(m.TableName()).
		Where(squirrel.Eq{"ID": id})
	if err := sqlutil.SelectOne(dbmap, query, m); err != nil {
		message := fmt.Sprintf("Could not find %s.", m.TableName())
		return httperr.New(http.StatusNotFound, message, err)
	}
	return nil
}