How can I use sqlx struct scan in situations where I am joining tables?

For example, lets say that Person has many Post and I want to get a struct that has a persons posts embedded into it as a slice.

I am imagining a DTO like this:

type Person struct {
    Id        string    `json:"id"`
    Name      string    `json:"name"`       
    Posts     []*Post   `json:"posts"`  
}

type Post struct {
    Id        string `json:"post_id"`
}

The SQL I imagine to use would be

SELECT 
    psn.id,
    psn.name,
    pst.id AS post_id
FROM
    person psn
JOIN posts pst ON pst.person_id = psn.id

Is this accomplishable in sqlx? How?

CodePudding user response：

Is this accomplishable in sqlx? How?

That's not a feature sqlx has, it isn't an ORM. It's just a convenience wrapper that among other things makes selecting rows into flat structs easier.

You'd either need to process the multiple rows per user yourself, or do two queries, first for the person and then for their posts.