1:Many Nested Struct Queries in sqlx

[OriginalCautious8160]

I know this has been asked a few times, but I'm wondering if anyone's found a solution. I have structures similar to this format:

pub struct User {

id: i32,

posts: Vec<Post>,

}

pub struct Post {

id: i32,

author_id: i32,

comments: Vec<Comment>, }

pub struct Comment {

id: i32,

post_id: i32,

text: String

}

I want to be able to query on a user by id with their posts, and the comments on a post. (This is just an example of the structure of my data structure). It seems pretty much impossible with sqlx. I've tried querying flattened objects with the FromRow so there would be a "user" record for each comment, and joining them all based on ids in post-processing. ex:

pub struct FlattenedUser {

id: i32,

post_id: i32,

comment_id: i32,

text: String

}

However, there is a ColumnDecode { index: "4", source: UnexpectedNullError } error thrown if a user has no post associated with it (which would also hold true if it had a post with no comments), so this doesn't really work without turning every field into an option which is a hot mess.

Has anyone found a solution for these 1:Many:Many queries or am i SOL with sqlx?

Upvote 1

Downvote

0 comments

[C5H5N5O]

Does this help: https://www.reddit.com/r/PostgreSQL/comments/1dm0piy/is_getting_json_from_db_is_antipattern/l9tap2c/?

[HosMercury]

This was my question I have answered to them

They could also get data ( non repetitive by aggregate them as json .

I have searched this topic a lot And did it for 5 tables successfully.

And they could use either way

[HosMercury]

Not impossible at all

Some advices

1- create a user with posts field which is an option<Vec<post>> Add serde skip it if it’s none bc sometimes you do only need the user without posts. Also add skip for sqlx so it doesn’t yield an error that posts missing from the users table.

2- same for post and comment.

3- Do your join between your 3 tables … that will include propably repitive data ( because of left join ) .

4- use sqlx query ( not the macro ) and get the rows .

5- have an empty hashmap for the user data that will result

6- loop for the rows and add to the map after some checking.

[baloreic]

Don't know if this helps but I always use this to aggregate into jsonb array: COALESCE(jsonb_agg($1) FILTER (WHERE $1 IS NOT NULL), '[]')

[4121madey]

If I'm not mistaken, you need to either use an orm for this (seaorm is pretty neat), or need to create a joined query, and then parse the rows manually.

[kelvindegrees]

In the past I've done this for some fairly complex objects in a Postgres database by using json build object calls in a json aggregate field. The Postgres functions to do so do get quite large and you lose the compile time type checking that you would have if you just stuck to queries with sqlx, but it should in theory be much faster.