Golang squirrel.Select函数代码示例

func getEvents(c *gin.Context) {
	user := c.MustGet("user").(*models.User)

	q := squirrel.Select("event.*, p.name as project_name").
		From("event").
		LeftJoin("project as p on event.project_id=p.id").
		OrderBy("created desc")

	projectObj, exists := c.Get("project")
	if exists == true {
		// limit query to project
		project := projectObj.(models.Project)
		q = q.Where("event.project_id=?", project.ID)
	} else {
		q = q.LeftJoin("project__user as pu on pu.project_id=p.id").
			Where("p.id IS NULL or pu.user_id=?", user.ID)
	}

	var events []models.Event

	query, args, _ := q.ToSql()
	if _, err := database.Mysql.Select(&events, query, args...); err != nil {
		panic(err)
	}

	for i, evt := range events {
		if evt.ObjectID == nil || evt.ObjectType == nil {
			continue
		}

		var q squirrel.SelectBuilder

		switch *evt.ObjectType {
		case "task":
			q = squirrel.Select("tpl.playbook as name").
				From("task").
				Join("project__template as tpl on task.template_id=tpl.id")
		default:
			continue
		}

		query, args, _ := q.ToSql()
		name, err := database.Mysql.SelectNullStr(query, args...)
		if err != nil {
			panic(err)
		}

		if name.Valid == true {
			events[i].ObjectName = name.String
		}
	}

	c.JSON(200, events)
}

func (d *DbMap) ManyToManyBuilder(m Model, mapping Mapping, selectStr string) (sq.SelectBuilder, error) {
	var slct string
	slct = selectStr
	if selectStr == "" {
		slct = "*"
	}
	other := mapping.OtherModel(m)
	kname := m.KeyName()
	k := m.Key()
	w := sq.Select("*").From(d.TableName(mapping)).Where(sq.Eq{kname: k})

	rows, err := d.Query(mapping, w)
	if err != nil {
		return sq.SelectBuilder{}, err
	}

	keys := make([]interface{}, 0)
	for _, r := range rows {
		if itr, ok := r.(Mapping); ok {
			keys = append(keys, itr.OtherKey(m))
		}
	}

	kname = other.KeyName()
	return sq.Select(slct).From(d.TableName(other)).Where(sq.Eq{kname: keys}), nil
}

// Wrapper for querying a Database struct
func Count(db *sql.DB, timeRange *database.TimeRange) (*database.Count, error) {
	// Query
	queryBuilder := sq.
		Select("COUNT(*) AS total", "COUNT(DISTINCT ip) AS uniqueCount").
		From("visits")

	// Add time constraints if timeRange provided
	if timeRange != nil {
		if !timeRange.Start.Equal(time.Time{}) {
			timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
			queryBuilder = queryBuilder.Where(timeQuery)
		}
		if !timeRange.End.Equal(time.Time{}) {
			timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
			queryBuilder = queryBuilder.Where(timeQuery)
		}
	}

	query, _, err := queryBuilder.ToSql()
	if err != nil {
		return nil, err
	}

	// Exec query
	count := database.Count{}
	err = db.QueryRow(query).Scan(&count.Total, &count.Unique)
	if err != nil {
		return nil, err
	}

	return &count, nil
}

// lookup() + ロック
func (self *Handle) LookupForUpdateX(obj Model, ids ...interface{}) bool {
	b := sq.Select(obj.GetFields()).From(obj.GetTable())

	for i, field := range obj.GetPrimaryKeys() {
		b = b.Where(sq.Eq{field: ids[i]})
	}
	b = b.Suffix("FOR UPDATE")
	s, args, err := b.ToSql()

	if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"ids":   ids,
			"err":   err,
		}).Panic("Fail To Build lookupForUpdate SQL")
	}

	row := self.Conn.QueryRow(s, args...)
	err = self.RowScan(obj, row)

	if err == sql.ErrNoRows {
		return false
	} else if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"ids":   ids,
			"err":   err,
		}).Panic("Fail to lookupForUpdate()")
	}

	return true
}

// LocationRecords ...
func (sdb *SQLiteNewtonDB) LocationRecords(ownerID, since, until int64, limit uint64, ascending bool) ([]LocationRecord, error) {
	builder := squirrel.Select("timestamp, latitude, longitude, ownerID").From("location_records")
	builder = builder.Where(squirrel.Eq{"owner_id": ownerID})
	if ascending {
		builder = builder.OrderBy("timestamp ASC")
	} else {
		builder = builder.OrderBy("timestamp DESC")
	}
	if limit > 0 {
		builder = builder.Limit(limit)
	}
	if since != -1 {
		builder = builder.Where(squirrel.Expr("timestamp > ?", since))
	}
	if until != -1 {
		builder = builder.Where(squirrel.Expr("timestamp < ?", until))
	}
	query, args, err := builder.ToSql()
	if err != nil {
		return nil, err
	}
	records := []LocationRecord{}
	err = sdb.db.Select(&records, query, args...)
	if err != nil {
		return nil, NewtonErr(err)
	}

	return records, nil
}

func userEventsGet(w http.ResponseWriter, r *http.Request) {
	user_id := mux.Vars(r)["id"]

	query, args, err := sq.Select("e.name", "e.start_time", "e.end_time", "e.active",
		"e.ongoing", "e.city", "e.latitude", "e.longitude",
		"ec.cat_cd", "ec.cat_val").
		From("user u").
		Join("user_events ue ON u.user_id = ue.user_id").
		Join("event e ON ue.event_id = e.event_id ").
		LeftJoin("event_category ec ON e.cat_cd = ec.cat_cd").
		Where(sq.Eq{"u.user_id": user_id}).
		ToSql()
	errors.CheckHttpErr(err != nil, "Internal Error", 500)

	events := make([]model.Event, 0)
	rows, err := db.DB.Queryx(query, args...)
	errors.CheckHttpErr(err != nil, "Internal Error", 500)
	for rows.Next() {
		var event model.Event
		err := rows.StructScan(&event)
		errors.CheckHttpErr(err != nil, "Internal Error", 500)
		events = append(events, event)
	}

	out, err := json.MarshalIndent(events, "", " ")
	errors.CheckHttpErr(err != nil, "Internal Error", 500)

	w.Write(out)
}

func ProjectMiddleware(c *gin.Context) {
	user := c.MustGet("user").(*models.User)

	projectID, err := util.GetIntParam("project_id", c)
	if err != nil {
		return
	}

	query, args, _ := squirrel.Select("p.*").
		From("project as p").
		Join("project__user as pu on pu.project_id=p.id").
		Where("p.id=?", projectID).
		Where("pu.user_id=?", user.ID).
		ToSql()

	var project models.Project
	if err := database.Mysql.SelectOne(&project, query, args...); err != nil {
		if err == sql.ErrNoRows {
			c.AbortWithStatus(404)
			return
		}

		panic(err)
	}

	c.Set("project", project)
	c.Next()
}

func InventoryMiddleware(c *gin.Context) {
	project := c.MustGet("project").(models.Project)
	inventoryID, err := util.GetIntParam("inventory_id", c)
	if err != nil {
		return
	}

	query, args, _ := squirrel.Select("*").
		From("project__inventory").
		Where("project_id=?", project.ID).
		Where("id=?", inventoryID).
		ToSql()

	var inventory models.Inventory
	if err := database.Mysql.SelectOne(&inventory, query, args...); err != nil {
		if err == sql.ErrNoRows {
			c.AbortWithStatus(404)
			return
		}

		panic(err)
	}

	c.Set("inventory", inventory)
	c.Next()
}

func EnvironmentMiddleware(c *gin.Context) {
	project := c.MustGet("project").(models.Project)
	envID, err := util.GetIntParam("environment_id", c)
	if err != nil {
		return
	}

	query, args, _ := squirrel.Select("*").
		From("project__environment").
		Where("project_id=?", project.ID).
		Where("id=?", envID).
		ToSql()

	var env models.Environment
	if err := database.Mysql.SelectOne(&env, query, args...); err != nil {
		if err == sql.ErrNoRows {
			c.AbortWithStatus(404)
			return
		}

		panic(err)
	}

	c.Set("environment", env)
	c.Next()
}

func selectTest(dbmap *gorp.DbMap) User {

	// パターン 1
	dbmap.AddTableWithName(User{}, "users").SetKeys(false, "Id")
	obj, err := dbmap.Get(User{}, 1)
	checkErr(err, "not found data!")

	u := obj.(*User)
	log.Printf("id : %d, name %s, score %d", u.Id, u.Name, u.Score)

	// パターン 2 (こちらの場合はSQLを書くのでAddTable不要)
	var user User // user := User{}
	err2 := dbmap.SelectOne(&user, "select * from users where id = 2")
	checkErr(err2, "not found data!")
	log.Printf("id : %d, name %s, score %d", user.Id, user.Name, user.Score)

	// パターン 3 (squirrelでSQL生成)
	sb := sql_builder.Select("*").From("users")
	sb = sb.Where(sql_builder.Eq{"id": 3})
	sql, args, sql_err := sb.ToSql()
	log.Println(sql)

	checkErr(sql_err, "SQL error!!")

	var user3 User // user := User{}
	err3 := dbmap.SelectOne(&user3, sql, args[0])
	checkErr(err3, "not found data!")
	log.Printf("id : %d, name %s, score %d", user3.Id, user3.Name, user3.Score)

	return user3
}

func (self *Handle) CountX(obj Model, buildFn func(sq.SelectBuilder) sq.SelectBuilder) int {
	b := sq.Select("count(*)").From(obj.GetTable())
	b = buildFn(b)
	s, args, err := b.ToSql()

	if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"err":   err,
		}).Panic("Fail To Build CountX SQL")
	}

	count := 0
	err = self.Conn.QueryRow(s, args...).Scan(&count)

	if err == sql.ErrNoRows {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"err":   err,
		}).Panic("Fail To Get Count SQL")
	} else if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"err":   err,
		}).Panic("Fail to CountX()")
	}

	return count
}

func (self *Handle) RetrieveX(obj Model, buildFn func(sq.SelectBuilder) sq.SelectBuilder) bool {
	b := sq.Select(obj.GetFields()).From(obj.GetTable())
	b = buildFn(b)
	s, args, err := b.ToSql()

	if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"err":   err,
		}).Panic("Fail To Build RetrieveX SQL")
	}

	row := self.Conn.QueryRow(s, args...)
	err = self.RowScan(obj, row)

	if err == sql.ErrNoRows {
		return false
	} else if err != nil {
		log.WithFields(log.Fields{
			"table": obj.GetTable(),
			"err":   err,
		}).Panic("Fail to RetrieveX()")
	}
	return true
}

func (d *DbMap) BelongsToBuilder(m Belongings, belong Model, selectStr string) sq.SelectBuilder {
	if selectStr == "" {
		selectStr = "*"
	}
	t := d.TableName(belong)
	kname := m.FKName(belong)
	k := m.FK(belong)
	return sq.Select(selectStr).From(t).Where(sq.Eq{kname: k})
}

func (d *DbMap) HasOneBuilder(m HasOne, theOne Model, selectStr string) sq.SelectBuilder {
	if selectStr == "" {
		selectStr = "*"
	}
	t := d.TableName(theOne)
	kname := m.FKName(theOne)
	k := m.FK(theOne)
	return sq.Select(selectStr).From(t).Where(sq.Eq{kname: k})
}

func (d *DbMap) HasManyBuilder(m HasMany, b Model, selectStr string) sq.SelectBuilder {
	var slct string
	slct = selectStr
	if selectStr == "" {
		slct = "*"
	}

	kname := m.FKNameInBelongings(b)
	return sq.Select(slct).From(d.TableName(b)).Where(sq.Eq{kname: m.FKInBelongings(b)})
}