mia.omg.lol
A Wrapped / Replay like for teal.fm and rocksky.app (currently on hiatus)
1use crate::SqliteConnection;
2use chrono::prelude::*;
3use rusqlite::params;
4
5#[derive(Debug)]
6pub struct TopAlbumsResp {
7 pub mbid: String,
8 pub name: String,
9 pub artist_credit: Option<String>,
10 pub release_group: i64,
11 pub count: i64,
12}
13
14pub fn get_top_albums(
15 conn: &SqliteConnection,
16 did: &str,
17 start: DateTime<Utc>,
18 end: DateTime<Utc>,
19 limit: i64,
20) -> rusqlite::Result<Vec<TopAlbumsResp>> {
21 let mut stmt = conn.prepare_cached(
22 r"WITH albums AS (
23 SELECT release_mbid, count(*) as count FROM scrobbles
24 WHERE did=$1 AND created_at BETWEEN $2 AND $3
25 GROUP BY release_mbid
26 ORDER BY count DESC LIMIT $4)
27 SELECT release.gid, release.name, artist_credit.name, release_group, count FROM release
28 INNER JOIN albums ON release_mbid = release.gid
29 INNER JOIN artist_credit ON artist_credit.id = release.artist_credit
30 ORDER BY count DESC",
31 )?;
32
33 let rows = stmt
34 .query_map(params![did, start, end, limit], |row| {
35 Ok(TopAlbumsResp {
36 mbid: row.get(0)?,
37 name: row.get(1)?,
38 artist_credit: row.get(2)?,
39 release_group: row.get(3)?,
40 count: row.get(4)?,
41 })
42 })?
43 .collect::<Result<_, _>>()?;
44
45 Ok(rows)
46}
47
48#[derive(Debug)]
49pub struct TopArtistResp {
50 pub mbid: String,
51 pub name: String,
52 pub count: i64,
53}
54pub fn get_top_artists(
55 conn: &SqliteConnection,
56 did: &str,
57 start: DateTime<Utc>,
58 end: DateTime<Utc>,
59 limit: i64,
60) -> rusqlite::Result<Vec<TopArtistResp>> {
61 let mut stmt = conn.prepare_cached(
62 r"WITH artists AS (
63 SELECT artist_credit_name.artist as id, count(*) as count FROM scrobbles
64 INNER JOIN track ON track.gid = track_mbid
65 INNER JOIN artist_credit_name ON artist_credit_name.artist_credit = track.artist_credit
66 WHERE did=$1 AND created_at BETWEEN $2 AND $3
67 GROUP BY artist_credit_name.artist
68 ORDER BY count DESC limit $4)
69 SELECT gid, name, count FROM artist
70 INNER JOIN artists ON artists.id = artist.id
71 ORDER BY count DESC"
72 )?;
73
74 let rows = stmt
75 .query_map(params![did, start, end, limit], |row| {
76 Ok(TopArtistResp {
77 mbid: row.get(0)?,
78 name: row.get(1)?,
79 count: row.get(2)?,
80 })
81 })?
82 .collect::<Result<_, _>>()?;
83
84 Ok(rows)
85}
86
87#[derive(Debug)]
88pub struct TopTracksResp {
89 pub mbid: String,
90 pub name: String,
91 pub artist_credit: Option<String>,
92 pub count: i64,
93}
94
95pub fn get_top_tracks(
96 conn: &SqliteConnection,
97 did: &str,
98 start: DateTime<Utc>,
99 end: DateTime<Utc>,
100 limit: i64,
101) -> rusqlite::Result<Vec<TopTracksResp>> {
102 let mut stmt = conn.prepare_cached(
103 r"WITH tracks AS (
104 SELECT track_mbid, count(*) as count FROM scrobbles
105 WHERE did=$1 AND created_at BETWEEN $2 AND $3
106 GROUP BY track_mbid
107 ORDER BY count DESC LIMIT $4)
108 SELECT track.gid, track.name, artist_credit.name, count FROM track
109 INNER JOIN tracks on track_mbid = track.gid
110 INNER JOIN artist_credit ON artist_credit.id = track.artist_credit
111 ORDER BY count DESC",
112 )?;
113
114 let rows = stmt
115 .query_map(params![did, start, end, limit], |row| {
116 Ok(TopTracksResp {
117 mbid: row.get(0)?,
118 name: row.get(1)?,
119 artist_credit: row.get(2)?,
120 count: row.get(3)?,
121 })
122 })?
123 .collect::<Result<_, _>>()?;
124
125 Ok(rows)
126}