Seralizing an INNER JOIN

[grifferz]

Backend

MySQL

Diesel version

2.3.5

Diesel Features

mysql

Operating System Version

Debian 13

Third party libraries

serde="1.0.228"

What do you want to do?

In the Relations guide there's an example of using belonging_to() to serialize the parent-child relationship between Books and Pages into a Vec<BookWithPages>:

#[derive(Serialize)]
struct BookWithPages {
    #[serde(flatten)]
    book: Book,
    pages: Vec<Page>,
}

// First query
let all_books = books::table.select(Book::as_select()).load(conn)?;

// Second query
// get all pages for all books
let pages = Page::belonging_to(&all_books)
    .select(Page::as_select())
    .load(conn)?;

// group the pages per book
let pages_per_book = pages
    .grouped_by(&all_books)
    .into_iter()
    .zip(all_books)
    .map(|(pages, book)| BookWithPages { book, pages })
    .collect::<Vec<BookWithPages>>();

There will be two SQL queries there.

Later, when covering INNER JOINs, there is an example of using a single query to return associated data from both the pages and books tables as a Vec<(Page, Book)>:

let page_with_book = pages::table
    .inner_join(books::table)
    .filter(books::title.eq("Momo"))
    .select((Page::as_select(), Book::as_select()))
    .load::<(Page, Book)>(conn)?;

My question is, is there an easy way to use the latter INNER JOIN approach but still end up with a serialized Vec<BookWithPages>?

Perhaps this is more of a general Rust data structures question than a Diesel question since I do appreciate that what is doing the work in the first example is the "group the pages per book" step, but I don't know enough to know, as I am a bit new at Rust in general.

Naively, all the data are there so I suppose one could just iterate through the Vec<(Page, Book)> and build one or more BookWithPages structs that way, but is there a more elegant / idiomatic way to do it?

Thanks!

Compile time error

No response

What code do you already have?

No response

Additional details

No response

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

Naively, all the data are there so I suppose one could just iterate through the Vec<(Page, Book)> and build one or more BookWithPages structs that way, but is there a more elegant / idiomatic way to do it?

That's what needs to be done. There is no way to skip this work. Diesel doesn't currently provide any API for this, so you need to roll your own implementation.

To do this you would need roughly the following:

1. Create a HashMap<i32, (Book, Vec<Page>)>
2. Iterate over all (Book, Page) pairs and:
   • Check if the book with the given id already exists in the hashmap, if not insert it with an empty page vector
   • Push the page to the page vector
3. Iterate again over the HashMap to create the BookWithPages list

There will be two SQL queries there.

I would like to comment on this: Yes there will be two queries, but at the same time you don't transmit any duplicated data. So depending on the exact data you request two queries might be faster than one in this particular case.

[grifferz]

Thanks! This was really helpful for my understanding.