Exemple #1
0
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)
}
Exemple #2
0
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)
}
Exemple #3
0
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)

}