How to access inner join data?

kallaway commented 5 years ago

Hello, I am sorry for a potentially silly question, but I've been stuck on this for a while now.

What is the correct way of accessing data that we 'joined' after we got the results back?

Here is an example from the docs: // Ultra complex query users, err := models.Users( Select("id", "name"), InnerJoin("credit_cards c on c.user_id = users.id"), Where("age > ?", 30), AndIn("c.kind in ?", "visa", "mastercard"), Or("email like ?",%aol.com%), GroupBy("id", "name"), Having("count(c.id) > ?", 2), Limit(5), Offset(6), ).All(ctx, db)

My question is, are we able to access users[0].c.Kind or similar data from the credit_cards table that we joined - after the fact? I see the example of: AndIn("c.kind in ?", "visa", "mastercard") where we access the data while still in the 'query building process', but can we somehow access that after the call has returned (and given values to 'users' and 'err') ?

If there is no way to do that, what would be an alternative approach? Making the requests to get the additional data as part of a bigger transaction (more than one call)?

I apologize for this basic question, but I couldn't find an answer to this so far.

aarondl commented 5 years ago

It's actually a little bit of a funny way that we do inner joins. I don't love it but it does work.

Take a look at the docs here: https://github.com/volatiletech/sqlboiler#binding

It shows how Bind() can be used to bind to a struct that contains more than one model. It's not pretty because in order for bind to understand which column belongs to what struct inside the struct they must be named with the . to allow Bind to figure it out. (Unique names are also a concern).

If you don't like that approach, you can just create a new struct with all the fields you want from the inner join and bind with that:

type myJoinStruct struct {
  PilotID int `boil:"pilot_id"`
  JetID   int `boil:"jet_id"`
  ...
}

qm.Select("pilot.id as pilot_id, jet.id as jet_id ...")

The last option of course is to just not use sqlboiler at all for this operation and use scan to ignore names and go based on ordinal position of the returned columns alone (this circumvents the need to as all your columns which is arguably the most annoying part about the inner join support in sqlboiler).

Hopefully that answers the question!

kallaway commented 5 years ago

@aarondl Thank you for your response! This definitely helps! :)

volatiletech / sqlboiler

How to access inner join data? #536