Golang DB.Order方法代码示例

// IndexSelection returns list of comments
func IndexSelection(req *http.Request, params martini.Params, r render.Render, db *gorm.DB) {
	limit := 40
	offset := 0
	query := req.URL.Query()
	rawPage, rawMaxResults := query.Get("page"), query.Get("maxResults")
	page, err := strconv.Atoi(rawPage)
	maxResults, err := strconv.Atoi(rawMaxResults)
	if err != nil {
		r.JSON(400, Error{400, "page and maxResults must be integer."})
	}

	limit = maxResults
	offset = (page - 1) * maxResults

	var comments []Selection
	db.Order("id desc").Limit(limit).Offset(offset).Find(&comments)
	r.JSON(200, comments)
}

func TeachersData(db *gorm.DB) ([]*models.Faculties, []*models.Departments, []*models.Teachers) {
	var faculties []*models.Faculties
	err := db.Find(&faculties).Error
	if err != nil {
		panic(err)
	}
	var departments []*models.Departments
	err = db.Find(&departments).Error
	if err != nil {
		panic(err)
	}
	var teachers []*models.Teachers
	err = db.Order("last_name").Find(&teachers).Error
	if err != nil {
		panic(err)
	}
	return faculties, departments, teachers
}

func GetOrderedWindowedQuery(db *gorm.DB, idfield string, id uint, start uint64, end uint64) *gorm.DB {

	var q *gorm.DB

	switch {
	case start > 0 && end > 0:
		q = db.Where(idfield+" = ? AND timestamp >= ? AND timestamp <= ?", id, start, end)
	case end > 0:
		q = db.Where(idfield+" = ? AND timestamp <= ?", id, end)
	case start > 0:
		q = db.Where(idfield+" = ? AND timestamp >= ?", id, start)
	default:
		q = db.Where(idfield+" = ?", id)
	}

	return q.Order("timestamp")

}

func RefreshStats(cache *Cache, db *gorm.DB) (err error) {
	m := &runtime.MemStats{}
	runtime.ReadMemStats(m)

	acq := m.Sys / 1024 / 1024
	used := m.Alloc / 1024 / 1024

	m = nil

	stats.GoRoutines = runtime.NumGoroutine()
	stats.MemoryAcq = int(acq)
	stats.MemoryUsed = int(used)

	stats.CacheMB = cache.GetSizeInMb()
	stats.CacheCount = cache.GetSize()

	stats.Rel = nil
	stats.Ta = nil

	err = db.Order("weight desc").Limit(10).Find(&stats.Ta).Error
	if err != nil {
		return
	}

	err = db.Order("hits desc").Limit(10).Find(&stats.Rel).Error
	if err != nil {
		return
	}

	row := db.Raw(HITCOUNT).Row()
	row.Scan(&stats.HitCount)

	row = db.Raw(TAGCOUNT).Row()
	row.Scan(&stats.TagCount)

	row = db.Raw(RELEASECOUNT).Row()
	row.Scan(&stats.ReleaseCount)

	return
}

func (p *QueryParams) PrepareQuery(searchRelation *gorm.DB) *gorm.DB {
	if len(p.Categories) != 0 && !contains(p.Categories, allCat) {
		searchRelation = searchRelation.Where("category_slug IN (?)", p.Categories)
	}

	if p.Page > 1 {
		searchRelation = searchRelation.Offset((p.Page - 1) * perPage)
	}

	if p.Sort == "newest" {
		searchRelation = searchRelation.Order("date desc")
	} else {
		searchRelation = searchRelation.Order("(total_views / POW(((EXTRACT(EPOCH FROM (now()-date)) / 3600)::integer + 2), 1.5)) desc")
	}

	if !p.StartDate.IsZero() {
		searchRelation = searchRelation.Where("date > ?", p.StartDate)
	}
	if !p.EndDate.IsZero() {
		searchRelation = searchRelation.Where("date < ?", p.EndDate)
	}

	return searchRelation.Limit(perPage)
}

func GetChatLogs(w http.ResponseWriter, r *http.Request) {
	chatLogsTempl, err := template.ParseFiles("views/admin/templates/chatlogs.html")
	if err != nil {
		logrus.Error(err.Error())
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	var messages []*models.ChatMessage
	values := r.URL.Query()

	room, err := strconv.Atoi(values.Get("room"))
	if err != nil && values.Get("room") != "" {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	steamID := values.Get("steamid")
	var from, to time.Time

	if values.Get("from") != "" {
		from, err = timestamp(values.Get("from"))
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	} else {
		from = time.Time{}
	}

	if values.Get("to") != "" {
		to, err = timestamp(values.Get("to"))
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	} else {
		to = time.Now()
	}

	order := values.Get("order")
	var results *gorm.DB

	if values.Get("room") == "" { //Retrieve all messages sent by a specific player
		if steamID == "" {
			http.Error(w, "No Steam ID given.", http.StatusBadRequest)
			return
		}

		playerID := getPlayerID(steamID)
		if playerID == 0 {
			http.Error(w, fmt.Sprintf("Couldn't find player with Steam ID %s", steamID), http.StatusNotFound)
			return
		}

		results = db.DB.Where("player_id = ? AND room = ? AND created_at >= ? AND created_at <= ?", playerID, room, from, to)
	} else if steamID == "" { //Retrieve all messages sent to a specfic room
		results = db.DB.Where("room = ? AND (created_at >= ? AND created_at <= ?)", room, from, to)
	} else { //Retrieve all messages sent to a specific room and a speficic player
		playerID := getPlayerID(steamID)
		if playerID == 0 {
			http.Error(w, fmt.Sprintf("Couldn't find player with Steam ID %s", steamID), http.StatusNotFound)
			return
		}

		results = db.DB.Where("player_id = ? AND room = ? AND created_at >= ? AND created_at <= ?", playerID, room, from, to)
	}

	if order == "Ascending" {
		err = results.Order("id").Find(&messages).Error
	} else if order == "Descending" {
		err = results.Order("id desc").Find(&messages).Error
	}

	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	for _, message := range messages {
		//err := db.DB.DB().QueryRow("SELECT name, profileurl FROM players WHERE id = $1", message.PlayerID).Scan(&message.Player.Name, &message.Player.ProfileURL)
		err := db.DB.DB().QueryRow("SELECT name, profileurl FROM players WHERE id = $1", message.PlayerID).Scan(&message.Player.Name, &message.Player.ProfileURL)
		if err != nil {
			logrus.Warning(err.Error())
		}
	}

	err = chatLogsTempl.Execute(w, messages)
	if err != nil {
		logrus.Error(err.Error())
		return
	}
}

func (uc ItemController) AllItems(db gorm.DB) []model.Element {
	var o []model.Element
	db.Order("id desc").Find(&o)
	return o
}

func GetChatLogs(w http.ResponseWriter, r *http.Request) {
	var messages []*chat.ChatMessage
	values := r.URL.Query()

	room, err := strconv.Atoi(values.Get("room"))
	if err != nil && values.Get("room") != "" {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	steamID := values.Get("steamid")
	var from, to time.Time

	if values.Get("from") != "" { //2006-01-02
		from, err = time.Parse("2006-01-02", values.Get("from"))
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	} else {
		from = time.Time{}
	}

	if values.Get("to") != "" {
		to, err = time.Parse("2006-01-02", values.Get("to"))
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	} else {
		to = time.Now()
	}

	order := values.Get("order")
	var results *gorm.DB

	if values.Get("room") == "" { //Retrieve all messages sent by a specific player
		if steamID == "" {
			http.Error(w, "No Steam ID given.", http.StatusBadRequest)
			return
		}

		playerID := getPlayerID(steamID)
		if playerID == 0 {
			http.Error(w, fmt.Sprintf("Couldn't find player with Steam ID %s", steamID), http.StatusNotFound)
			return
		}

		results = db.DB.Preload("Player").Where("player_id = ? AND room = ? AND created_at >= ? AND created_at <= ?", playerID, room, from, to)
	} else if steamID == "" { //Retrieve all messages sent to a specfic room
		results = db.DB.Preload("Player").Where("room = ? AND (created_at >= ? AND created_at <= ?)", room, from, to)
	} else { //Retrieve all messages sent to a specific room and a speficic player
		playerID := getPlayerID(steamID)
		if playerID == 0 {
			http.Error(w, fmt.Sprintf("Couldn't find player with Steam ID %s", steamID), http.StatusNotFound)
			return
		}

		results = db.DB.Preload("Player").Where("player_id = ? AND room = ? AND created_at >= ? AND created_at <= ?", playerID, room, from, to)
	}

	if order == "Ascending" {
		err = results.Order("id").Find(&messages).Error
	} else if order == "Descending" {
		err = results.Order("id desc").Find(&messages).Error
	}

	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	err = chatLogsTempl.Execute(w, messages)
	if err != nil {
		logrus.Error(err)
	}
}

func PopularIndex(db gorm.DB, r render.Render) {
	media := []models.Media{}
	db.Order("played desc").Limit(15).Find(&media)
	r.JSON(http.StatusOK, media)
}

func LatestIndex(db gorm.DB, r render.Render) {
	media := []models.Media{}
	db.Order("created_at desc").Limit(15).Find(&media)
	r.JSON(http.StatusOK, media)
}

func (b *Box) RetrieveOrdered(db *gorm.DB) ([]Box, error) {
	var bs []Box
	err := db.Order("start_date asc").Where(b).Find(&bs, b.Base.BuildQuery()).Error

	return bs, err
}

// getCurrentQuestionWithTX returns the current question using database transaction.
func getCurrentQuestionWithTX(tx *gorm.DB) (*Question, error) {
	question := &Question{}
	err := tx.Order("started_at").Last(question).Error
	return question, err
}

/*获取所有记录*/
func (p *Point) GetAll(db gorm.DB) []Point {
	points := []Point{}
	db.Order("create_at desc").Find(&points)
	return points
}