go-pg
One-to-many relation using join
Hello,
I have a problem with the following context:
type A struct {
Id int
Bs []*B `pg:"rel:has-many"`
}
type B struct {
Id int
AId int
}
When I want to Get A and A's Bs by doing:
a := A{Id: 1}
tx.Model(&a).Relation("Bs").WherePK().Select()
Then 2 SELECT are run on the DB, one for the A and one for the Bs.
I experimented in SQL and I saw that in my case, joining is more efficient.
However if I run:
var a A
tx.Query(&a, `SELECT "a.id", "bs"."id" AS "bs__id" FROM "as" as "a" LEFT JOIN "bs" AS "b" ON "b"."aid" = "a"."id" WHERE "a"."id" = (?)`, 1)
Then I get:
pg: can't find column=bs__id in model=A
It seems like go-pg is not detecting the relation between A and B and is looking for a column called bs__id in table as .
How to do if I want this one-to-many relation to be done with a join in a way go-pg understands?
Exemple code :
var db Postgres
type Patient struct {
PatientID string `pg:",pk,notnull,unique"`
PatientName string
Studies []Study `pg:"rel:has-many"`
}
type Study struct {
StudyInstanceUID string `pg:",pk,notnull,unique"`
StudyID string
PatientPatientID string
Patient *Patient `pg:"rel:has-one"`
}
func init() {
// load config
config := conf.Load("test")
// init db
var err error
db, err = New(config)
if err != nil {
log.Fatal(err)
}
err = db.CreateSchema((*Patient)(nil), (*Study)(nil))
if err != nil {
log.Fatal(err)
}
p1 := Patient{
PatientID: "1",
PatientName: "test1",
Studies: []Study{{
StudyID: "s1",
StudyInstanceUID: "uid1",
PatientPatientID: "1"}},
}
p2 := Patient{
PatientID: "2",
PatientName: "test2",
Studies: []Study{{
StudyID: "s2",
StudyInstanceUID: "uid2",
PatientPatientID: "2",
}},
}
_, err = db.Conn.Model(&p1).Insert()
if err != nil {
log.Fatal(err)
}
_, err = db.Conn.Model(&p1.Studies[0]).Insert()
if err != nil {
log.Fatal(err)
}
_, err = db.Conn.Model(&p2).Insert()
if err != nil {
log.Fatal(err)
}
_, err = db.Conn.Model(&p2.Studies[0]).Insert()
if err != nil {
log.Fatal(err)
}
}
func TestRelation(t *testing.T) {
p := Patient{
PatientID: "1",
}
err := db.Conn.Model(&p).Relation("Studies").WherePK().Select()
if err != nil {
t.Error(err)
}
}
Result :
Jan 26 19:30:58.064 [INFO] CREATE TABLE IF NOT EXISTS "patients" ("patient_id" text NOT NULL UNIQUE, "patient_name" text, PRIMARY KEY ("patient_id"), UNIQUE ("patient_id"))
Jan 26 19:30:58.083 [INFO] CREATE TABLE IF NOT EXISTS "studies" ("study_instance_uid" text NOT NULL UNIQUE, "study_id" text, "patient_patient_id" text, PRIMARY KEY ("study_instance_uid"), UNIQUE ("study_instance_uid"), FOREIGN KEY ("patient_patient_id") REFERENCES "patients" ("patient_id"))
Jan 26 19:30:58.100 [INFO] INSERT INTO "patients" ("patient_id", "patient_name") VALUES ('1', 'test1')
Jan 26 19:30:58.115 [INFO] INSERT INTO "studies" ("study_instance_uid", "study_id", "patient_patient_id") VALUES ('uid1', 's1', '1')
Jan 26 19:30:58.129 [INFO] INSERT INTO "patients" ("patient_id", "patient_name") VALUES ('2', 'test2')
Jan 26 19:30:58.143 [INFO] INSERT INTO "studies" ("study_instance_uid", "study_id", "patient_patient_id") VALUES ('uid2', 's2', '2')
=== RUN TestRelation
Jan 26 19:30:58.157 [INFO] SELECT "patient"."patient_id", "patient"."patient_name" FROM "patients" AS "patient" WHERE "patient"."patient_id" = '1'
Jan 26 19:30:58.172 [INFO] SELECT "study"."study_instance_uid", "study"."study_id", "study"."patient_patient_id" FROM "studies" AS "study" WHERE ("study"."patient_patient_id" IN ('1'))
--- PASS: TestRelation (0.03s)
PASS
How to do if I want this one-to-many relation to be done with a join in a way go-pg understands?
Such joins are only supported for has-one and belongs-to relations. Has-many is not supported and never was.
And I don't have plans working on this in near future.
https://pg.uptrace.dev/orm/has-many-relation/
Its mentioned here that its supported. but its still not working for me.
https://pg.uptrace.dev/orm/has-many-relation/
Its mentioned here that its supported. but its still not working for me.
Are you running into the same issue, or one very similar?
I am facing the same issue, Relation() is not even producing the "left join" since its not supported.