Problems loading data for many-to-many relation

[idubinskiy]

Given the following schema and code:

schema "public" {}

table "pilots" {
  schema = schema.public

  column "id" {
    type = bigserial
  }

  column "name" {
    type = text
  }

  primary_key {
    columns = [column.id]
  }
}

table "jets" {
  schema = schema.public

  column "id" {
    type = bigserial
  }

  column "name" {
    type = text
  }

  primary_key {
    columns = [column.id]
  }
}

table "pilots_jets" {
  schema = schema.public

  column "pilot_id" {
    type = bigint
  }

  column "jet_id" {
    type = bigint
  }

  primary_key {
    columns = [
      column.pilot_id,
      column.jet_id,
    ]
  }

  foreign_key "pilot_id" {
    columns     = [column.pilot_id]
    ref_columns = [table.pilots.column.id]
  }

  foreign_key "jet_id" {
    columns     = [column.jet_id]
    ref_columns = [table.jets.column.id]
  }
}

models.Pilots.Query(context.Background(), db, models.ThenLoadPilotJets()).All()

The resulting data (as JSON with back-referencing disabled and struct tag options changed) looks like:

[
  {
    "id": 1,
    "name": "Alice",
    "_rel": {
      "Jets": null
    }
  },
  {
    "id": 2,
    "name": "Bob",
    "_rel": {
      "Jets": [
        {
          "id": 0,
          "name": "",
          "_rel": {
            "Pilots": null
          }
        }
      ]
    }
  },
  {
    "id": 3,
    "name": "Carol",
    "_rel": {
      "Jets": [
        {
          "id": 0,
          "name": "",
          "_rel": {
            "Pilots": null
          }
        },
        {
          "id": 0,
          "name": "",
          "_rel": {
            "Pilots": null
          }
        }
      ]
    }
  }
]

Note the zero-value id and name attributes on the Jets elements.

Using additional ThenLoad or Preload doesn't work:

models.Pilots.Query(context.Background(), db, models.ThenLoadPilotJets(models.ThenLoadPilotsJetJet())).All()
// object []*struct { models.Jet; RelatedID int64 "db:\"related_pilots.ID\"" } cannot load PilotsJetJet

models.Pilots.Query(context.Background(), db, models.ThenLoadPilotJets(models.PreloadPilotsJetJet())).All()
// pq: missing FROM-clause entry for table "pilots_jets"

Using bob.Debug for the latter yields this SQL:

SELECT 
"pilots"."id" AS "id", "pilots"."name" AS "name"
FROM "pilots" AS "pilots"


SELECT 
"pilots_jets"."pilot_id" AS "related_pilots.ID", "jets_5299"."id" AS "jets_5299.id", "jets_5299"."name" AS "jets_5299.name"
FROM "jets" AS "jets"
LEFT JOIN "jets" AS "jets_5299" ON ("pilots_jets"."jet_id" = "jets_5299"."id")
INNER JOIN "pilots_jets" ON ("jets"."id" = "pilots_jets"."jet_id")
WHERE (("pilots_jets"."pilot_id") IN (($1), ($2), ($3)))

0: 1
1: 2
2: 3

Iterating over the returned pilots and calling LoadPilotsJets() for each one is a workaround but definitely less convenient in the code. Another "workaround" is having an additional column in the associative table, in which case the generated code doesn't have a pass-through and the following code works:

models.Pilots.Query(context.Background(), db, models.ThenLoadPilotPilotsJets(models.PreloadPilotsJetJet())).All()

[
  {
    "id": 1,
    "name": "Alice",
    "_rel": {
      "PilotsJets": null
    }
  },
  {
    "id": 2,
    "name": "Bob",
    "_rel": {
      "PilotsJets": [
        {
          "pilotID": 2,
          "jetID": 1,
          "notes": "",
          "_rel": {
            "Pilot": null,
            "Jet": {
              "id": 1,
              "name": "Foo",
              "_rel": {
                "PilotsJets": null
              }
            }
          }
        }
      ]
    }
  },
  {
    "id": 3,
    "name": "Carol",
    "_rel": {
      "PilotsJets": [
        {
          "pilotID": 3,
          "jetID": 2,
          "notes": "Lorem ipsum",
          "_rel": {
            "Pilot": null,
            "Jet": {
              "id": 2,
              "name": "Bar",
              "_rel": {
                "PilotsJets": null
              }
            }
          }
        },
        {
          "pilotID": 3,
          "jetID": 3,
          "notes": "",
          "_rel": {
            "Pilot": null,
            "Jet": {
              "id": 3,
              "name": "Baz",
              "_rel": {
                "PilotsJets": null
              }
            }
          }
        }
      ]
    }
  }
]

Note that data is populated for the notes attribute (where it exists in the DB) as well as the Jet id and name attributes.

[stephenafamo]

SELECT 
"pilots_jets"."pilot_id" AS "related_pilots.ID",

This first column is supposed to be used to match the related models appropriately. Unfortunately, It seems something isn't quite right with the generated code.

I'll find some time to look into this in the coming weeks.

[idubinskiy]

Thank you. If it helps, here's the debug SQL for the first code (which runs but doesn't populate the jets data):

SELECT 
"pilots"."id" AS "id", "pilots"."name" AS "name"
FROM "pilots" AS "pilots"


SELECT 
"pilots_jets"."pilot_id" AS "related_pilots.ID"
FROM "jets" AS "jets"
INNER JOIN "pilots_jets" ON ("jets"."id" = "pilots_jets"."jet_id")
WHERE (("pilots_jets"."pilot_id") IN (($1), ($2), ($3)))

0: 1
1: 2
2: 3

It's not even trying to query columns from the jets table (or even just jet_id from pilots_jets). In the "workaround" case with the extra column it does query the columns from pilots_jets with just models.ThenLoadPilotPilotsJets(). So it seems like it's an issue specific to the automatic passthrough.

[stephenafamo]

This issue should be fixed by #139, try it out and let me know.

I assume you were just trying workarounds, but please note that models.PreloadPilotsJetJet() and models.ThenPilotsJetJet() are meant to be used when you're querying or loading from the pilot_jets table.

models.ThenLoadPilotJets() will be loading from the jets table, which is why you get the error missing FROM-clause entry for table "pilots_jets"