如何将SQLx查询结果行加载到含结构体向量的结构体中？

方法1：优化查询 + HashMap分组简化代码 #

首先调整SQL查询，补充author_id和book_id（用于分组和构建Book结构体），同时将查询主表改为author（避免遗漏无书籍的作者）：

#[derive(Debug, sqlx::FromRow)]
struct AuthorBookRow {
    author_id: Uuid,
    name: String,
    dob: String,
    book_id: Option<Uuid>,
    title: Option<String>,
    published: Option<String>,
}

let rows = sqlx::query_as::<Postgres, AuthorBookRow>(r#"
    select
        author.author_id,
        author.name,
        author.dob,
        book.book_id,
        book.title,
        book.published
    from
        author
        left join book using (author_id)
"#)
.fetch_all(&mut *dbc)
.await?;

接着用迭代器的fold方法，借助HashMap完成分组和结构体构建，无需手动编写大量循环样板：

use std::collections::HashMap;

let authors: Vec<Author> = rows
    .into_iter()
    .fold(HashMap::new(), |mut map, row| {
        // 若当前作者不存在于Map中，初始化Author结构体
        let author = map.entry(row.author_id).or_insert_with(|| Author {
            author_id: row.author_id,
            name: row.name,
            dob: row.dob,
            books: Vec::new(),
        });
        // 若当前行包含书籍数据，添加到对应作者的书籍列表
        if let (Some(book_id), Some(title), Some(published)) = (row.book_id, row.title, row.published) {
            author.books.push(Book {
                book_id,
                author_id: row.author_id,
                title,
                published,
            });
        }
        map
    })
    .into_values() // 提取Map中的所有Author值
    .collect();

方法2：使用itertools库简化分组逻辑 #

如果觉得手动写fold不够直观，可以借助itertools crate的group_by功能，进一步简化分组代码：

1. 先在Cargo.toml添加依赖：

itertools = "0.12"

2. 实现代码：

use itertools::Itertools;

// 先获取AuthorBookRow类型的查询结果rows，同方法1

let authors: Vec<Author> = rows
    .into_iter()
    .group_by(|row| row.author_id) // 按author_id分组
    .into_iter()
    .map(|(author_id, group)| {
        let mut group_iter = group.into_iter();
        // 取分组内第一行初始化Author基础信息
        let first_row = group_iter.next().unwrap();
        let mut books = Vec::new();
        
        // 处理第一行的书籍数据（如果有）
        if let (Some(book_id), Some(title), Some(published)) = (first_row.book_id, first_row.title, first_row.published) {
            books.push(Book {
                book_id,
                author_id,
                title,
                published,
            });
        }
        
        // 处理分组内剩余行的书籍数据
        for row in group_iter {
            if let (Some(book_id), Some(title), Some(published)) = (row.book_id, row.title, row.published) {
                books.push(Book {
                    book_id,
                    author_id,
                    title,
                    published,
                });
            }
        }
        
        Author {
            author_id,
            name: first_row.name,
            dob: first_row.dob,
            books,
        }
    })
    .collect();

方法3：结合PostgreSQL聚合函数 + 自定义FromRow（进阶） #

如果希望直接从查询结果映射为Author结构体，无需在Rust端分组，可以利用PostgreSQL的聚合函数将书籍数据打包为JSON数组，再通过自定义sqlx::FromRow实现反序列化：

1. 调整SQL查询，使用array_agg和json_build_object聚合书籍数据：

select
    author.author_id,
    author.name,
    author.dob,
    array_agg(json_build_object(
        'book_id', book.book_id,
        'title', book.title,
        'published', book.published
    )) as books
from author
left join book using (author_id)
group by author.author_id, author.name, author.dob

2. 为Author实现自定义FromRow：

use serde::Deserialize;

// 确保Book实现Deserialize用于JSON反序列化
#[derive(Debug, Deserialize)]
struct Book {
   book_id: Uuid,
   author_id: Uuid,
   title: String,
   published: String,
}

impl sqlx::FromRow<'_, sqlx::postgres::PgRow> for Author {
    fn from_row(row: &sqlx::postgres::PgRow) -> sqlx::Result<Self> {
        let author_id: Uuid = row.get("author_id");
        let name: String = row.get("name");
        let dob: String = row.get("dob");
        // 从查询结果中获取JSON数组并反序列化为Vec<Book>
        let books_json: Vec<serde_json::Value> = row.get("books");
        
        let books: Vec<Book> = books_json
            .into_iter()
            .filter(|v| !v.is_null()) // 过滤掉无书籍时的null值
            .map(|v| serde_json::from_value(v).unwrap())
            .collect();

        Ok(Author {
            author_id,
            name,
            dob,
            books,
        })
    }
}

3. 直接查询映射为Vec<Author>：

let authors: Vec<Author> = sqlx::query_as::<Postgres, Author>(/* 上述SQL语句 */)
    .fetch_all(&mut *dbc)
    .await?;

内容的提问来源于stack exchange，提问作者mmachenry