I'm writing a list endpoint using Go and sqlx. This endpoint can also take filters.
I want to get a list of Posts and their associated Attachments.
I am struggling to figure out how I would go about doing this.
Here is a snippet of my Post model, and I am trying to get a slice of Post.
type Post struct {
ID int `db:"id"`
// ...
Attachments []*Attachment
}
Here's Attachment
type Attachment struct {
ID int `db:"id"`
// ...
}
I have already got my getOne endpoint working, where it was simple -
I just had to do a simple Select query to get the Post by id, and to get the associated attachments I just did a join of attachments and posts_attachments.
Unfortunately, I am struggling to figure out how I can get a list of Posts with Attachments when each Post could have either 0 or more than 0 attachments.
Is it easily possible to do this? I am just looking for a tip on how I can start with the list part since I know how to get one, just not how to get many.
One approach I got was:
Get a list of posts.
For each id in that list, do the attachments lookup and if there are >0 append them to that post item's Attachment field.
However, is this bad? It seems too easy but also amateur because its doing so many lookups per item (I cap at 50 results per lookup). :(