jamtrack/internal/query/ranking.go

// Package query contains database queries for the jamtrack application.
package query

import (
	"github.com/pocketbase/dbx"
	"github.com/pocketbase/pocketbase/core"
)

// SongRank holds the aggregated play statistics for a single song.
type SongRank struct {
	ID            string `db:"id"`
	Artist        string `db:"artist"`
	Title         string `db:"title"`
	PlayedCount   int    `db:"played_count"`
	ProposedCount int    `db:"proposed_count"`
}

// FetchRanking returns songs ordered by play frequency (played_count desc,
// proposed_count desc). If locationID is non-empty the results are filtered
// to jams at that location.
func FetchRanking(app core.App, locationID string) ([]SongRank, error) {
	var rows []SongRank

	if locationID != "" {
		err := app.DB().
			NewQuery(`
				SELECT s.id, s.artist, s.title,
				       COALESCE(SUM(sl.played), 0) AS played_count,
				       COUNT(sl.id)                 AS proposed_count
				FROM   setlist sl
				JOIN   songs s  ON s.id  = sl.song
				JOIN   jams  j  ON j.id  = sl.jam
				WHERE  j.location = {:location}
				GROUP  BY s.id
				ORDER  BY played_count DESC, proposed_count DESC
			`).
			Bind(dbx.Params{"location": locationID}).
			All(&rows)
		return rows, err
	}

	err := app.DB().
		NewQuery(`
			SELECT s.id, s.artist, s.title,
			       COALESCE(SUM(sl.played), 0) AS played_count,
			       COUNT(sl.id)                 AS proposed_count
			FROM   setlist sl
			JOIN   songs s ON s.id = sl.song
			GROUP  BY s.id
			ORDER  BY played_count DESC, proposed_count DESC
		`).
		All(&rows)
	return rows, err
}