示例#1
0
文件: storage.go 项目: husio/apps
func NotesByOwner(s pg.Selector, ownerID, limit, offset int) ([]*Note, error) {
	var notes []*Note
	err := s.Select(&notes, `
		SELECT * FROM notes
		WHERE owner_id = $1 AND (expire_at < $2 OR expire_at IS NULL)
		ORDER BY created_at DESC
		LIMIT $3 OFFSET $4
	`, ownerID, time.Now(), limit, offset)
	return notes, pg.CastErr(err)
}
示例#2
0
文件: storage.go 项目: husio/apps
func Pastes(s pg.Selector, limit, offset int64) ([]*Paste, error) {
	var pastes []*Paste
	err := s.Select(&pastes, `
		SELECT
			id, created, updated,
			substring(content from 0 for 500) AS content
		FROM pastes
		ORDER BY updated DESC
		LIMIT $1 OFFSET $2
	`, limit, offset)
	return pastes, pg.CastErr(err)
}
示例#3
0
文件: source.go 项目: husio/apps
func Subscriptions(s pg.Selector, subscriber int64, limit, offset int64) ([]*Subscription, error) {
	var subs []*Subscription
	err := s.Select(&subs, `
		SELECT sub.*, src.*
		FROM subscriptions sub
			INNER JOIN sources src ON sub.source_id = src.source_id
		WHERE
			sub.subscriber_id = $1
		ORDER BY sub.created DESC
		LIMIT $2 OFFSET $3
	`, subscriber, limit, offset)
	return subs, pg.CastErr(err)
}
示例#4
0
文件: source.go 项目: husio/apps
func Entries(s pg.Selector, subscriber int64, limit, offset int64) ([]*Entry, error) {
	var entries []*Entry
	err := s.Select(&entries, `
		SELECT e.*, src.*
		FROM entries e
			OUTER JOIN sources src ON src.source_id = e.source_id
			OUTER JOIN subscriptions sub ON sub.source_id = src.source_id
		WHERE
			sub.subscriber_id = $1
		ORDER BY e.created DESC
		LIMIT $2 OFFSET $3
	`, subscriber, limit, offset)
	return entries, pg.CastErr(err)
}
示例#5
0
文件: storage.go 项目: husio/apps
// Comments return slice of comments that match given query criteria.
func Comments(s pg.Selector, o CommentsOpts) ([]*Comment, error) {
	if o.Offset < 0 {
		o.Offset = 0
	}
	q := qb.Q("SELECT * FROM comments").Limit(o.Limit, o.Offset).OrderBy("created DESC")
	if o.TopicID != 0 {
		q.Where("topic_id = ?", o.TopicID)
	}
	if o.AuthorID != "" {
		q.Where("author_id = ?", o.AuthorID)
	}
	query, args := q.Build()
	query = sqlx.Rebind(sqlx.DOLLAR, query)
	var comments []*Comment
	if err := s.Select(&comments, query, args...); err != nil {
		return nil, pg.CastErr(err)
	}

	return comments, nil
}
示例#6
0
文件: storage.go 项目: husio/apps
// Topics return slice of topics that match given query criteria.
func Topics(s pg.Selector, o TopicsOpts) ([]*TopicWithAuthor, error) {
	if o.Offset < 0 {
		o.Offset = 0
	}
	q := qb.Q(`
		SELECT t.*, a.*
		FROM topics t LEFT JOIN accounts a ON t.author_id = a.account_id
	`).Limit(o.Limit, o.Offset).OrderBy("t.created ASC")

	if len(o.Tags) > 0 {
		// TODO
	}
	if !o.OlderThan.IsZero() {
		q.Where("t.created < ?", o.OlderThan)
	}

	query, args := q.Build()
	query = sqlx.Rebind(sqlx.DOLLAR, query)
	var topics []*TopicWithAuthor
	if err := s.Select(&topics, query, args...); err != nil {
		return nil, pg.CastErr(err)
	}
	return topics, nil
}