Method FindToPager

models/book.go:233–303

分页查询指定用户的书籍 按照最新的进行排序

(pageIndex, pageSize, memberId int, wd string, PrivatelyOwned ...int)

//分页查询指定用户的书籍
//按照最新的进行排序
func (m *Book) FindToPager(pageIndex, pageSize, memberId int, wd string, PrivatelyOwned ...int) (books []*BookResult, totalCount int, err error) {
	var args = []interface{}{memberId}
	relationship := NewRelationship()
	o := orm.NewOrm()
	sqlCount := "SELECT COUNT(book.book_id) AS total_count FROM " + m.TableNameWithPrefix() + " AS book LEFT JOIN " +
		relationship.TableNameWithPrefix() + " AS rel ON book.book_id=rel.book_id AND rel.member_id = ? WHERE rel.relationship_id > 0 "
	if len(PrivatelyOwned) > 0 {
		sqlCount = sqlCount + " and book.privately_owned=" + strconv.Itoa(PrivatelyOwned[0])
	}

	if wd = strings.TrimSpace(wd); wd != "" {
		wd = "%" + wd + "%"
		sqlCount = sqlCount + " and (book.book_name like ? or book.description like ?)"
		args = append(args, wd, wd)
	}

	err = o.Raw(sqlCount, args...).QueryRow(&totalCount)
	if err != nil {
		beego.Error(err)
		return
	}

	offset := (pageIndex - 1) * pageSize
	sqlQuery := "SELECT book.*,rel.member_id,rel.role_id,m.account as create_name FROM " + m.TableNameWithPrefix() + " AS book" +
		" LEFT JOIN " + relationship.TableNameWithPrefix() + " AS rel ON book.book_id=rel.book_id AND rel.member_id = ? " +
		" LEFT JOIN " + NewMember().TableNameWithPrefix() + " AS m ON rel.member_id=m.member_id " +
		" WHERE rel.relationship_id > 0 %v ORDER BY book.book_id DESC LIMIT " + fmt.Sprintf("%d,%d", offset, pageSize)

	cond := []string{}
	if wd != "" { // 不需要处理 wd 和 args，因为在上面已处理过
		cond = append(cond, " and (book.book_name like ? or book.description like ?)")
	}

	if len(PrivatelyOwned) > 0 {
		cond = append(cond, " and book.privately_owned="+strconv.Itoa(PrivatelyOwned[0]))
	}
	sqlQuery = fmt.Sprintf(sqlQuery, strings.Join(cond, " "))

	_, err = o.Raw(sqlQuery, args...).QueryRows(&books)
	if err != nil {
		beego.Error("分页查询书籍列表 => ", err, sqlQuery)
		return
	}

	if err == nil && len(books) > 0 {
		sql := "SELECT m.account,doc.modify_time FROM md_documents AS doc LEFT JOIN md_members AS m ON doc.modify_at=m.member_id WHERE book_id = ? ORDER BY doc.modify_time DESC LIMIT 1 "

		for index, book := range books {
			var text struct {
				Account    string
				ModifyTime time.Time
			}

			err = o.Raw(sql, book.BookId).QueryRow(&text)
			if err == nil {
				books[index].LastModifyText = text.Account + " 于 " + text.ModifyTime.Format("2006-01-02 15:04:05")
			}