Frédéric G. MARAND
6f3321371d
- Fixed song add form (3 HTMX bugs: reset on typeahead, wrong query param, artist-only find-or-create) - Added Played checkbox to add form; inline edit (✎/Save/Cancel) for setlist rows - View fixes: date format, location name instead of ID in jam list, notes on location detail, song count column, location name links to detail - Title typeahead + cross-filtering (artist↔title filter each other) - Fixed datalist accumulation bug: HTMX 2.0 inherits hx-swap from parent; added explicit hx-swap="innerHTML" on both inputs - Setlist sort by id (ULID = insertion order); created rejected by PocketBase - Location delete guard: OnRecordDelete hook blocks if jams exist; FK enforcement is app-level not SQLite-level Loose ends to pick up: - Focus-after-add not user-confirmed yet (setTimeout approach) - jams.participants and setlist.position still in DB schema but unused in UI (optional migration to clean up) - Logout flow and dashboard ranking not browser-tested
54 lines
1.5 KiB
Go
54 lines
1.5 KiB
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
|
|
}
|