Golang DB.Limit方法代码示例

//分页读取数据
func (this *Controller) GetTablePaginatorData(tableDB *gorm.DB, req *http.Request, limit int) {

	p := req.URL.Query().Get("p")
	pageNo, _ := strconv.Atoi(p)
	if pageNo == 0 {
		pageNo = 1
	}
	//var limit int64 = 10 //每页10行显示
	var offset = (pageNo - 1) * limit //起始位置

	tbDB := tableDB.Limit(limit).Offset(offset)
	var count int
	tbDB.Count(count)

	this.Data["paginator"] = NewPaginator(req, int(limit), count)

}

// GetAll Function, Fetch All Users with Offset and Limit
func GetAll(limit, offset int, DB *gorm.DB) (users []User, err []helper.ErrorMessage) {

	_err := DB.Limit(limit).Offset(offset).Find(&users).Error
	if _err != nil {
		err = append(err, helper.ErrorMessage{
			Code:    400,
			Source:  helper.SourceErrors{},
			Title:   "Failed Fetch All User",
			Details: _err.Error(),
		})

		return users, err
	}

	for i, user := range users {
		var profile Profile
		DB.Model(&user).Related(&profile)
		users[i].Profile = profile
	}

	return users, nil
}

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)
}

// postlistQueryBuilder returns the same pointer passed as first argument, with new specified options setted
// If the user parameter is present, it's intentend to be the user browsing the website.
// So it will be used to fetch the following list -> so we can easily find the posts on a bord/project/home/ecc made by the users that "user" is following
func postlistQueryBuilder(query *gorm.DB, options *PostlistOptions, user ...*User) *gorm.DB {
	if options == nil {
		return query.Limit(20)
	}

	if options.N > 0 && options.N < 20 {
		query = query.Limit(options.N)
	} else {
		query = query.Limit(20)
	}

	userOK := len(user) == 1 && user[0] != nil

	if !options.Followers && options.Following && userOK { // from following + me
		following := user[0].NumericFollowing()
		if len(following) != 0 {
			query = query.Where("\"from\" IN (? , ?)", following, user[0].Counter)
		}
	} else if !options.Following && options.Followers && userOK { //from followers + me
		followers := user[0].NumericFollowers()
		if len(followers) != 0 {
			query = query.Where("\"from\" IN (? , ?)", followers, user[0].Counter)
		}
	} else if options.Following && options.Followers && userOK { //from friends + me
		follows := new(UserFollower).TableName()
		query = query.Where("\"from\" IN ( (SELECT ?) UNION  (SELECT \"to\" FROM (SELECT \"to\" FROM "+follows+" WHERE \"from\" = ?) AS f INNER JOIN (SELECT \"from\" FROM "+follows+" WHERE \"to\" = ?) AS e on f.to = e.from) )", user[0].Counter, user[0].Counter, user[0].Counter)
	}

	if options.Language != "" {
		if options.User {
			query = query.Where(new(UserPost).TableName()+".lang = ?", options.Language)
		} else {
			query = query.Where(new(ProjectPost).TableName()+".lang = ?", options.Language)
		}
	}

	if options.Older != 0 && options.Newer != 0 {
		query = query.Where("hpid BETWEEN ? AND ?", options.Newer, options.Older)
	} else if options.Older != 0 {
		query = query.Where("hpid < ?", options.Older)
	} else if options.Newer != 0 {
		query = query.Where("hpid > ?", options.Newer)
	}

	return query
}

func GetSubscriptions(page int, subs *[]Subscription, db *gorm.DB) error {
	pageSize := 10
	return db.Limit(10).Offset(pageSize * page).Find(subs).Error
}