Help me to get the relation.

[jnsand]

type Friend struct {
	tableName struct{}  `pg: tbl_friends,alias:tf"`
	Id        uuid.UUID `pg:"id, type:uuid"`
	AccountId uuid.UUID `pg:"account_id,type:uuid"`
	FriendId  uuid.UUID `pg:"friend_id,type:uuid"`
	Profile   *Profile  `pg:"rel:has-one, fk:friend_id, join_fk:account_id"`
}

type Profile struct {
	tableName         struct{}  `pg:"tbl_profiles,alias:tp"`
	Id                uuid.UUID `pg:"id,type:uuid"`
	AccountId         uuid.UUID `pg:"account_id,type:uuid"`
	PlayerCode        string    `pg:"player_code"`
	PlayerName        string    `pg:"player_name"`
}

var friends []models.Friend
var accountID uuid.UUID
db.Model(&friends).Relation("Profile").Where("tf.account_id = ?", accountID).Select()

After executing this query, friends.Profile is always nil.

My query result is same with below SQL query?

SELECT * FROM tbl_friends tf LEFT JOIN tbl_profiles tp ON tf.friend_id = tp.account_id WHERE tf.account_id = '1111-1111-1111-'

Thanks.

[elliotcourant]

Can you provide a brief example where you've seeded your data using go-pg as well?

[jnsand]

This is profile table

5cb416a5-62d4-4be6-8f19-8a44bc39d89a 68fbb252-b848-4f28-a41f-5348d9f4af30 123456 Henry
23707e79-15d0-48fa-9ddc-c248caf794eb 68fbb252-b848-4f28-a41f-5348d9f4af31 222222 Green
9d7852ce-bcd3-4b2d-8820-00759ce79e3e 11eba2d0-16e3-40bd-92a6-4f9ef52985ec OT8M04B Denys

and this is friend table

4b5e6e95-ee8e-4f44-b72d-83c3658f684c 11eba2d0-16e3-40bd-92a6-4f9ef52985ec 68fbb252-b848-4f28-a41f-5348d9f4af30

so my query is
SELECT * FROM tbl_friends tf LEFT JOIN tbl_profiles tp ON tf.friend_id = tp.account_id WHERE tf.account_id = '11eba2d0-16e3-40bd-92a6-4f9ef52985ec'

var friends []models.Friend
var accountID uuid.UUID
db.Model(&friends).Relation("Profile").Where("tf.account_id = ?", accountID).Select()

After executing this command, i want friends[0].Profile.PlayerName to be "Henry"

[elliotcourant]

So for the profiles table you have:

ID	Account ID	Player Code	Player Name
5cb416a5-62d4-4be6-8f19-8a44bc39d89a	68fbb252-b848-4f28-a41f-5348d9f4af30	123456	Henry
23707e79-15d0-48fa-9ddc-c248caf794eb	68fbb252-b848-4f28-a41f-5348d9f4af31	222222	Green
9d7852ce-bcd3-4b2d-8820-00759ce79e3e	11eba2d0-16e3-40bd-92a6-4f9ef52985ec	OT8M04B	Denys

And for the friends table you have:

ID	Account ID	Friend ID
4b5e6e95-ee8e-4f44-b72d-83c3658f684c	11eba2d0-16e3-40bd-92a6-4f9ef52985ec	68fbb252-b848-4f28-a41f-5348d9f4af30

---

So Denys should be friends with Henry by this data right?

SELECT 
  profiles.*
FROM friends
LEFT JOIN profiles ON friends.friend_id = profiles.account_id
WHERE friends.account_id = '11eba2d0-16e3-40bd-92a6-4f9ef52985ec'

Should yield you the row for Henry

You're saying that your query does work but the go-pg one does not? If that is the case then I'd have to dig through how go-pg is building the query with the custom join column for that since it is a bit weird.

But if your query is also not producing the results then something else is fundamentally wrong.

[elliotcourant]

I should also note as im not sure if this was intentional.

db.Model(&friends).Relation("Profile").Where("tf.account_id = ?", accountID).Select()

In order to read results back from the DB into data for go-pg, you have to pass &friends to the Select() as well.

Model() tells go-pg what its working with, but not what to do with it.
Select() tells go-pg to read the results of whatever query back into the passed data (by pointer).

[jnsand]

I use DBeaver to execute a raw SQL query and it produces expected result.
I think go code is right but defining relation in model is wrong.
What do you think?

[jnsand]

After executed go code,
friends[0].AccountId and friends[0].FriendId is valid but friends[0].Profile is nil.
Can you please recheck how I defined model?