diesel: inner_join forward query

Question

I am learning to use inner_join for forward query

pub async fn hello(pool: web::Data<DbPool>) -> HttpResult {
    let conn = pool.get().map_err(ErrorInternalServerError)?;

    let data: Vec<(models::Book, models::User)> = books::table
        .inner_join(users::table)
        .load::<(models::Book, models::User)>(&conn)
        .map_err(ErrorInternalServerError)?;

    Ok(HttpResponse::Ok().json(data))
}

I get the correct data

[
    [{
        "id": 1,
        "name": "js",
        "user_id": 1
    }, {
        "id": 1,
        "username": "admin"
    }],
    [{
        "id": 2,
        "name": "rust",
        "user_id": 1
    }, {
        "id": 1,
        "username": "admin"
    }]
]

But the structure of this json data is not what I want

I want this nested structure

[{
    "id": 1,
    "name": "js",
    "user": {
        "id": 1,
        "username": "admin"
    }
}, {
    "id": 2,
    "name": "rust",
    "user": {
        "id": 1,
        "username": "admin"
    }
}]

I don't know how to convert, is there a best practice

---

More information

schema:

table! {
    books (id) {
        id -> Unsigned<Bigint>,
        name -> Varchar,
        user_id -> Unsigned<Bigint>,
    }
}

table! {
    users (id) {
        id -> Unsigned<Bigint>,
        username -> Varchar,
    }
}

joinable!(books -> users (user_id));

allow_tables_to_appear_in_same_query!(
    books,
    users,
);

models:

#[derive(Identifiable, Queryable, Associations, Serialize, Deserialize, Debug, Clone)]
#[belongs_to(User, foreign_key = "user_id")]
#[table_name = "books"]
pub struct Book {
    #[serde(skip_deserializing)]
    pub id: PK,
    pub name: String,
    pub user_id: PK,
}

#[derive(Identifiable, Queryable, Serialize, Deserialize, Debug, Clone)]
#[table_name = "users"]
pub struct User {
    pub id: PK,
    pub username: String,
}

diesel version

diesel = { version = "1.4.4", features = ["mysql", "r2d2", "chrono", "numeric"] }

Answer 1

An easy way is to use #[serde(flatten)]

#[derive(Serialize, Deserialize, Debug, Clone)]
pub struct BookWithUser<B, U> {
    #[serde(flatten)]
    book: B,
    user: U,
}

impl<B, U> From<(B, U)> for BookWithUser<B, U> {
    fn from((b, u): (B, U)) -> Self {
        Self { book: b, user: u }
    }
}

#[derive(Identifiable, Associations, Queryable, Serialize, Deserialize, Debug, Clone)]
#[belongs_to(models::user::User, foreign_key = "user_id")]
#[table_name = "books"]
pub struct Book {
     ...
}

then use like this

#[get("/with_user")]
pub async fn list_with_user(pool: web::Data<DbPool>) -> HttpResult {
    use schema::*;
    let res = web::block(move || -> Result<_, DbError> {
        let conn = pool.get()?;
        let res = books::table
            .inner_join(users::table)
            .load::<(mods::book::Book, mods::user::User)>(&conn)
            .map(|x| x.into_iter().map(mods::book::BookWithUser::from))?
            .collect::<Vec<_>>();
        Ok(res)
    })
    .await?
    .map_err(ErrorInternalServerError)?;

    Ok(HttpResponse::Ok().json(res))
}

Answer 2

your real problem is with serde crate

if you have data type like this (String, i32, ...) serde will create array in output see following code

fn main() {
   let val = ("Hello", 123, 2.5);

   let result = serde_json::to_string(&val).unwrap();
   println!("{}", result); // ["Hello",123,2.5] 
}

so if you wanna solve it you can do something like this

first make your custom model

struct User {
   id:      i32,
   username: String,
}

struct ResponseModel {
   id:   i32,
   name: String,
   user: User,
} 

now implement From trait for ResponseModel

impl From<(models::Book, models::User)> for ResponseModel {
    fn from(values: (models::Book, models::User)) -> Self {
        Self {
            id: values.0.id,
            name: values.0.name,
            user: User {
                id: values.1.id,
                username: values.1.username,
            },
        }
    }
}

now change hello fn like this

pub async fn hello(pool: web::Data<DbPool>) -> HttpResult {
   let conn = pool.get().map_err(ErrorInternalServerError)?;

   let data: Vec<ResponseModel> = books::table
       .inner_join(users::table)
       .load::<(models::Book, models::User)>(&conn)
       .map(|x| x.into_iter().map(ResponseModel::from).collect())
       .map_err(ErrorInternalServerError)?;

   Ok(HttpResponse::Ok().json(data))
}