func NotesByOwner(s pg.Selector, ownerID, limit, offset int) ([]*Note, error) {
var notes []*Note
err := s.Select(¬es, `
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)
}
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)
}
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)
}
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)
}
// 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
}
// 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
}