func (db *PodcastDBReader) SelectByChannelID(channelID, page int64) (*models.PodcastList, error) {
q := "SELECT COUNT(id) FROM podcasts WHERE channel_id=$1"
var numRows int64
if err := db.QueryRowx(q, channelID).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT id, title, enclosure_url, description, pub_date, source
FROM podcasts
WHERE channel_id=$1
ORDER BY pub_date DESC
OFFSET $2 LIMIT $3`
err := sqlx.Select(
db,
&result.Podcasts,
q,
channelID,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
func (db *PodcastDBReader) SelectAll(page int64) (*models.PodcastList, error) {
var numRows int64
q := "SELECT COUNT(id) FROM podcasts"
if err := db.QueryRowx(q).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
ORDER BY p.pub_date DESC
OFFSET $1 LIMIT $2`
err := sqlx.Select(
db,
&result.Podcasts,
q,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
func (db *PodcastDBReader) SelectBookmarked(userID, page int64) (*models.PodcastList, error) {
q := `SELECT COUNT(DISTINCT(p.id)) FROM podcasts p
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1`
var numRows int64
if err := db.QueryRowx(q, userID).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1
GROUP BY p.id, p.title, c.title, c.image, b.id
ORDER BY b.id DESC
OFFSET $2 LIMIT $3`
err := sqlx.Select(
db,
&result.Podcasts,
q,
userID,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
func (db *defaultPodcastDBImpl) SelectPlayed(userID, page int64) (*models.PodcastList, error) {
q, _ := db.ps.Get("select_played_podcasts_count.sql")
var numRows int64
if err := db.QueryRow(q, userID).Scan(&numRows); err != nil {
return nil, sqlErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q, _ = db.ps.Get("select_played_podcasts.sql")
err := db.Select(
&result.Podcasts,
q,
userID,
result.Page.Offset,
result.Page.PageSize)
return result, sqlErr(err, q)
}
