sqlx in golang – Is it possible to map joined tables?

Issue

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?

Solution

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.

Answered By – donatJ

Answer Checked By – Robin (GoLangFix Admin)

Leave a Reply

Your email address will not be published.