Recommended Pattern for Joining Tables and Producing Nested Structs

[ahmed-said-jax]

I've been following the (absolutely fantastic) realword-axum-sqlx guide, and there's an interesting pattern I noticed here.

Essentially, we create a struct (in this case it's called ArticleFromQuery) mimicking the output of an SQL query returning rows with data from multiple tables, and then we manually transform that into a nested struct suitable for an API response. In this case, it's the method into_article() which takes the ArticleFromQuery and transforms it into an Article. This repo is a couple of years old - is there a newer pattern to handle this situation with the query*! macros? I know this is pretty adjacent to these discussions, but the answers in there seem slightly against the paradigms of sqlx.

Thanks!

[abonander]

We used that pattern quite a lot internally. There's nothing really wrong with it besides the amount of boilerplate.

At the end of the day, SQL just doesn't have great support for returning complex structured data in a way we can reliably typecheck, so needing a transformation step is understandable.

Making the query macros work with FromRow and the flatten attribute would improve the situation, that's discussed in #514.

[ahmed-said-jax]

Totally agree, just wanted to see if you had come up with some alternative in the past couple years. As you said, SQL isn't well-suited for that type of operation, so I'm happy with writing some boilerplate.

Thanks!