Idiomatic method for returning many-to-many models

[FrankelJb]

Hi Sqlx,

What is the idiomatic way of returning models in a many-to-many relationship? Specifically when returning a single struct as opposed to return tuple pairs.

Given Video, Tags, VideoWithTags and VideoTags models where VideoTags is a join table containing the key-pairs of video_id and tag_id. The VideoWithTags has all the same field as Videos with an extra tags: Vec<Tag> field. I tried to return everything in a single await chain using and_then. However, the inner .map returns an error that it is not async. This is what I tried:

let recs = sqlx::query(
    r#"
    SELECT *
    FROM videos"#,
)
.map(|rec: SqliteRow| VideoWithTags {
    id: rec.get(0),
...
    tags: Vec::new(),
})
.fetch_all(pool)
.await
.and_then(|videos| {
    videos
        .into_iter()
        .map(|video| {
            let tags = sqlx::query(
                r#"SELECT tags.label
    FROM tags
    JOIN video_tags ON tags.id = video_tags.tag_id
    WHERE video_tags.video_id = ?1"#,
            )
            .bind(video.id)
            .map(|row: SqliteRow| NewTag {
                id: None,
                label: row.get(0),
            })
            .fetch_all(pool)
            .await?;
            video.tags = tags;
            Ok(video)
        })
        .collect()
});

The error is: await is only allowed inside async functions and blocks only allowed inside async functions and blocks. I understand that map is sync and I've managed to do it using two sqlx::query blocks but I liked the idea of using and_then.

[abonander]

If you use futures::TryFutureExt you can lift the .await down below .and_then(), and then you can make your map closure return an async block and use futures::future::try_join_all() to run all the subsequent fetches concurrently:

use futures::{TryFutureExt, future::try_join_all};

let recs = sqlx::query(
    r#"
    SELECT *
    FROM videos"#,
)
.map(|rec: SqliteRow| VideoWithTags {
    id: rec.get(0),
...
    tags: Vec::new(),
})
.fetch_all(pool)
.and_then(|videos| {
    try_join_all(videos
        .into_iter()
        .map(|video| async {
            let tags = sqlx::query(
                r#"SELECT tags.label
    FROM tags
    JOIN video_tags ON tags.id = video_tags.tag_id
    WHERE video_tags.video_id = ?1"#,
            )
            .bind(video.id)
            .map(|row: SqliteRow| NewTag {
                id: None,
                label: row.get(0),
            })
            .fetch_all(pool)
            .await?;
            video.tags = tags;
            Ok(video)
        })
        .collect())
})
.await;

We don't .await the try_join_all() because .and_then() when called on a Future is expecting the closure to return a Future, so it's redundant.

[FrankelJb]

Thanks! Closing as answered