Add duration of episode (in shows) in 'episodes-by-season' query

[rodrimarchese]

We fetch episodes, and we make data to show from there like this:

But, we need to make like multiple querys (for EACH episode), to have the only thing we need to display in details page that is the time of each episode. We are making as many querys as episodes are in that season and receiving data like this:

here are only 3 eps, but when the season has 12... or 24....

Maybe there's a way to add the duration of episode to the first query so theres no need to make a lot of querys. Of course this may make more slow when you select an episode, because you would trigger the query there and you need to wait for the path to the file and that stuff... but maybe the balance is positive.

[Times you see a Season's episodes list] > [Times you select an episode to watch]

Maybe another solution is posible. This is only a suggestion I thought.

Ty!

[niamu]

I started work on this and I'll share my patch here for how to accomplish this SQL query, but the more I worked on this the less convinced I became that this will be worthwhile with the way the frontend works currently.

The frontend currently makes 2 requests per episode of a season;

1. SelectMediaFileEpisode requests /api/v1/media/:id which this issue points out is only used for the progress and duration fields.
2. SelectMediaFile requests /api/v1/media/:id/files which only makes use of the id and target_file for each item in the array response. SelectMediaFile should ideally be lazy and only fetch data when media has been selected to be played, but it is currently greedy and fetches this data even though it will likely go unused.

This is a significant refactor and I personally feel it will be more effective to approach this problem differently rather than proceed with the fix I had started on as I suspect we'll just need to modify these queries again as clients evolve in their needs. GraphQL-like query engines are one possible solution to this that would allow for the client to fetch only what it needs.

Ultimately, we'd need to make a decision on what solution suits the myriad of clients that Dim may have in development and need to support in the future that doesn't involve breaking API changes for every new feature and is a performant user experience.

---

With that said, here's the backend patch I worked on:

diff --git a/dim-web/src/routes/tv.rs b/dim-web/src/routes/tv.rs
index 6b3a7ee1..7e0cfe15 100644
--- a/dim-web/src/routes/tv.rs
+++ b/dim-web/src/routes/tv.rs
@@ -1,4 +1,5 @@
 use crate::AppState;
+use axum::Extension;
 use axum::response::IntoResponse;
 use axum::extract::Json;
 use axum::extract::Path;
@@ -7,6 +8,7 @@ use axum::extract::State;
 use dim_database::DatabaseError;
 use dim_database::episode::{Episode, UpdateEpisode};
 use dim_database::season::{Season, UpdateSeason};
+use dim_database::user::User;

 use http::StatusCode;

@@ -69,24 +71,39 @@ pub async fn patch_season_by_id(
 pub async fn get_season_episodes(
     State(AppState { conn, .. }): State<AppState>,
     Path(id): Path<i64>,
+    Extension(user): Extension<User>,
 ) -> Result<impl IntoResponse, AuthError> {
     let mut tx = conn.read().begin().await.map_err(DatabaseError::from)?;
-    #[derive(serde::Serialize)]
+    #[derive(serde::Serialize, sqlx::FromRow)]
     pub struct Record {
         pub id: i64,
         pub name: String,
         pub thumbnail_url: Option<String>,
         pub episode: i64,
+        pub progress: i64,
+        pub duration: i64,
     }

-    let result = sqlx::query_as!(Record,
-        r#"SELECT episode.id as "id!", _tblmedia.name, assets.local_path as thumbnail_url, episode.episode_ as "episode!"
+    let result = sqlx::query_as::<_, Record>(
+        r#"SELECT
+            episode.id,
+            _tblmedia.name,
+            assets.local_path as thumbnail_url,
+            episode.episode_ as "episode",
+            COALESCE(progress.delta, 0) as "progress",
+            MAX(mediafile.duration) as "duration"
         FROM episode
+        LEFT JOIN progress ON (progress.media_id = episode.id AND progress.user_id = ?)
         INNER JOIN _tblmedia on _tblmedia.id = episode.id
         LEFT JOIN assets ON assets.id = _tblmedia.backdrop
-        WHERE episode.seasonid = ?"#,
-        id
-    ).fetch_all(&mut tx).await.unwrap_or_default();
+        INNER JOIN mediafile ON mediafile.media_id = _tblmedia.id
+        WHERE episode.seasonid = ?
+        GROUP BY "episode"
+        ORDER BY "episode" ASC"#
+    )
+    .bind(user.id)
+    .bind(id)
+    .fetch_all(&mut tx).await.map_err(DatabaseError::from)?;

     Ok(axum::response::Json(json!(&result)).into_response())
 }