Golang DB.Select方法代码示例

//.........这里部分代码省略.........

	if opts.BeforeId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id < $%d", len(args)+1))
		args = append(args, opts.BeforeId)
	}
	if opts.AfterId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id > $%d", len(args)+1))
		args = append(args, opts.AfterId)
	}

	if opts.FavoriteOnly {
		whereSlice = append(whereSlice, "af.article_id IS NOT NULL")
	}

	if !opts.BeforeDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("(a.date IS NULL OR a.date < $%d)", len(args)+1))
		args = append(args, opts.BeforeDate)
	}

	if !opts.AfterDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("a.date > $%d", len(args)+1))
		args = append(args, opts.AfterDate)
	}

	if len(whereSlice) > 0 {
		renderData.Where = "WHERE " + strings.Join(whereSlice, " AND ")
	}

	sortingField := sorting.Field()
	sortingOrder := sorting.Order()

	fields := []string{}

	if opts.IncludeScores && opts.HighScoredFirst {
		field := "asco.score"
		if sortingOrder == data.DescendingOrder {
			field += " DESC"
		}
		fields = append(fields, field)
	}

	if opts.UnreadFirst {
		fields = append(fields, "read")
	}

	switch sortingField {
	case data.SortById:
		fields = append(fields, "a.id")
	case data.SortByDate:
		fields = append(fields, "a.date")
	}
	if len(fields) > 0 {
		renderData.Order = " ORDER BY " + strings.Join(fields, ", ")

		if sortingOrder == data.DescendingOrder {
			renderData.Order += " DESC"
		}
	}

	if opts.Limit > 0 {
		renderData.Limit = fmt.Sprintf(" LIMIT $%d OFFSET $%d", len(args)+1, len(args)+2)
		args = append(args, opts.Limit, opts.Offset)
	}

	buf := util.BufferPool.GetBuffer()
	defer util.BufferPool.Put(buf)

	if err := getArticlesTemplate.Execute(buf, renderData); err != nil {
		u.Err(fmt.Errorf("Error executing get-articles template: %v", err))
		return
	}

	sql := buf.String()
	var data []data.Article
	logger.Debugf("Articles SQL:\n%s\nArgs:%v\n", sql, args)
	if err := dbo.Select(&data, sql, args...); err != nil {
		u.Err(err)
		return
	}

	ua = make([]content.UserArticle, len(data))
	for i := range data {
		ua[i] = u.Repo().UserArticle(u)
		ua[i].Data(data[i])
	}

	processors := u.Repo().ArticleProcessors()
	if !opts.SkipProcessors && len(processors) > 0 {
		for _, p := range processors {
			if opts.SkipSessionProcessors {
				if _, ok := p.(processor.ProxyHTTP); ok {
					continue
				}
			}
			ua = p.ProcessArticles(ua)
		}
	}

	return
}

func articleIds(u content.User, dbo *db.DB, logger webfw.Logger, opts data.ArticleIdQueryOptions, join, where string, args []interface{}) (ids []data.ArticleId) {
	if u.HasErr() {
		return
	}

	s := dbo.SQL()
	var err error
	if getArticleIdsTemplate == nil {
		getArticleIdsTemplate, err = template.New("article-ids-sql").
			Parse(s.User.GetArticleIdsTemplate)

		if err != nil {
			u.Err(fmt.Errorf("Error generating article-ids template: %v", err))
			return
		}
	}

	renderData := articleIdsData{}
	containsUserFeeds := !opts.UnreadOnly && !opts.FavoriteOnly

	if containsUserFeeds {
		renderData.Join += s.User.GetArticleIdsUserFeedsJoin
	} else {
		if opts.UnreadOnly {
			renderData.Join += s.User.GetArticleIdsUnreadJoin
		}
		if opts.FavoriteOnly {
			renderData.Join += s.User.GetArticleIdsFavoriteJoin
		}
	}

	if opts.UntaggedOnly {
		renderData.Join += s.User.GetArticleIdsUntaggedJoin
	}

	if join != "" {
		renderData.Join += " " + join
	}

	args = append([]interface{}{u.Data().Login}, args...)

	whereSlice := []string{}

	if opts.UnreadOnly {
		whereSlice = append(whereSlice, "au.article_id IS NOT NULL AND au.user_login = $1")
	}
	if opts.FavoriteOnly {
		whereSlice = append(whereSlice, "af.article_id IS NOT NULL AND af.user_login = $1")
	}
	if opts.UntaggedOnly {
		whereSlice = append(whereSlice, "uft.feed_id IS NULL")
	}

	if where != "" {
		whereSlice = append(whereSlice, where)
	}

	if opts.BeforeId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id < $%d", len(args)+1))
		args = append(args, opts.BeforeId)
	}
	if opts.AfterId > 0 {
		whereSlice = append(whereSlice, fmt.Sprintf("a.id > $%d", len(args)+1))
		args = append(args, opts.AfterId)
	}

	if !opts.BeforeDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("(a.date IS NULL OR a.date < $%d)", len(args)+1))
		args = append(args, opts.BeforeDate)
	}

	if !opts.AfterDate.IsZero() {
		whereSlice = append(whereSlice, fmt.Sprintf("a.date > $%d", len(args)+1))
		args = append(args, opts.AfterDate)
	}

	if len(whereSlice) > 0 {
		renderData.Where = "WHERE " + strings.Join(whereSlice, " AND ")
	}

	buf := util.BufferPool.GetBuffer()
	defer util.BufferPool.Put(buf)

	if err := getArticleIdsTemplate.Execute(buf, renderData); err != nil {
		u.Err(fmt.Errorf("Error executing article-ids template: %v", err))
		return
	}

	sql := buf.String()

	logger.Debugf("Article ids SQL:\n%s\nArgs:%v\n", sql, args)
	if err := dbo.Select(&ids, sql, args...); err != nil {
		u.Err(err)
		return
	}

	return
}

func getArticles(u content.User, dbo *db.DB, logger webfw.Logger, sorting content.ArticleSorting, columns, join, where, order string, args []interface{}, paging ...int) (ua []content.UserArticle) {
	if u.HasErr() {
		return
	}

	sql := dbo.SQL("get_article_columns")
	if columns != "" {
		sql += ", " + columns
	}

	sql += dbo.SQL("get_article_tables")
	if join != "" {
		sql += " " + join
	}

	sql += dbo.SQL("get_article_joins")

	args = append([]interface{}{u.Data().Login}, args...)
	if where != "" {
		sql += " AND " + where
	}

	sortingField := sorting.Field()
	sortingOrder := sorting.Order()

	fields := []string{}
	if order != "" {
		fields = append(fields, order)
	}
	switch sortingField {
	case data.SortById:
		fields = append(fields, "a.id")
	case data.SortByDate:
		fields = append(fields, "a.date")
	}
	if len(fields) > 0 {
		sql += " ORDER BY "

		sql += strings.Join(fields, ",")

		if sortingOrder == data.DescendingOrder {
			sql += " DESC"
		}
	}

	if len(paging) > 0 {
		limit, offset := pagingLimit(paging)

		sql += fmt.Sprintf(" LIMIT $%d OFFSET $%d", len(args)+1, len(args)+2)
		args = append(args, limit, offset)
	}

	var data []data.Article
	logger.Debugf("Articles SQL:\n%s\nArgs:%q\n", sql, args)
	if err := dbo.Select(&data, sql, args...); err != nil {
		u.Err(err)
		return
	}

	ua = make([]content.UserArticle, len(data))
	for i := range data {
		ua[i] = u.Repo().UserArticle(u)
		ua[i].Data(data[i])
	}

	return
}