Load multiple models with true eager loading

[alex-ntousias]

After playing around with SQLBoiler a bit, I realized that qm.Load doesn't really do eager loading. It triggers a separate SQL query to load the additional model.

I have the following models:

type TableA struct {
  ID string `boil:"id" json:"id" toml:"id" yaml:"id"`
  Value string `boil:"value" json:"value" toml:"value" yaml:"value"`
  TableBID string `boil:"tableb_id" json:"tableb_id" toml:"tableb_id" yaml:"tableb_id"`
}

type TableB struct {
  ID string `boil:"id" json:"id" toml:"id" yaml:"id"`
  Value string `boil:"value" json:"value" toml:"value" yaml:"value"`
}

where the TableA.TableBID is a foreign key to TableB.

How can I achieve true eager loading and given a table A id, to load both the corresponding TableA and TableB entries with a single SQL query?

I've already tried something like the following:

type IntermediateModel struct {
  TableA TableA `boil:"tablea"`
  TableB TableB `boil:"tablea"`
}

func retrieveModels(id string) IntermediateModel {
  var response IntermediateModel
  err := queries.Raw(`
    SELECT tablea.* AS tablea, tableb.* AS tableb
    FROM tablea
    INNER JOIN tableb ON tableb.id = tablea.tableb_id
    WHERE tablea.id=$1`, id).Bind(ctx, exec, &response)
  
  return response
}

but it looks like this messes up the fields, because they have the same names.

[stephenafamo]

You have to prefix the alias names correctly for bind to work.

type IntermediateModel struct {
  TableA TableA `boil:"tablea,bind"` // Note ",bind"
  TableB TableB `boil:"tableb,bind"`
}

func retrieveModels(id string) IntermediateModel {
  var response IntermediateModel
  err := queries.Raw(`
    SELECT 
        tablea.id AS "tablea.id",
        tablea.value AS "tablea.value",
        tableb.id AS "tablea.tableb_id",
        tableb.id AS "tableb.id",
        tableb.value AS "tableb.value"
    FROM tablea
    INNER JOIN tableb ON tableb.id = tablea.tableb_id
    WHERE tablea.id=$1`, id).Bind(ctx, exec, &response)
  
  return response
}

I understand this is not very user friendly, but this is the best SQLBoiler can do right now.
I have looked into making this possible, but it is quite difficult without a lot of breaking changes which may not be desirable to most users.
If you think of a way to implement it, I'll be happy to review/merge a PR.

If your project is new, you can consider my other package Bob which can do what you want. In some ways, Bob is what I would make SQLBoiler if I could start from scratch.

[alex-ntousias]

@stephenafamo Thank you for your response.
Unfortunately, we are already invested in SQLBoiler and switching to a different library/framework would require a significant engineering effort which we don't really want to do right now.

What we ended up doing to overcome the above was to create a helper method that expands the columns we need with appropriate aliases so that SQLBoiler can resolve naming clashes.

So, the above example would become:

type IntermediateModel struct {
  TableA models.TableA `boil:"tablea,bind"`
  TableB models.TableB `boil:"tableb,bind"`
}

func retrieveModels(id string) IntermediateModel {
  var response IntermediateModel
  err := models.NewQuery(
    qm.Select(withAlias(models.TableATableColumns)...),
    qm.Select(withAlias(models.TableBTableColumns)...),
    qm.From(models.TableNames.TableA),
    qm.InnerJoin(......),
    models.TableAWhere.ID.Eq(id)).Bind(ctx, exec, &response)

The withAlias helper method uses reflection to get all the column names from the XxxxxTableColumns struct that is generated by SQLBoiler.
Ideally, it would be good to have the string array of columns already generated by SQLBoiler.

We've noticed that such an array is generated (i.e. xxxxxAllColumns), but for some reason it has private visibility. Any idea why it's private?

[stephenafamo]

It is used in some of the model methods, and so it is private to avoid someone modifying it unintentionally which will lead to all sorts of problems.

Also, any exported variable now has to be maintained going forward to prevent breaking backwards incompatibility.

Another way to prevent unnecessary reflection is to add your own template and include a function/variable/method that returns the aliased table columns.

[alex-ntousias]

Another way to prevent unnecessary reflection is to add your own template and include a function/variable/method that returns the aliased table columns.

Hmm, that's very interesting. I've tried to look it up in the documentation but I can't find anything.
Could you direct me to a doc or show me an example on how I could do it?

[stephenafamo]

The simple answer is to pass the path to the template directory using the --template flag.

Note that this will completely replace the default templates, so you should copy all the templates from the repository first (or send a PR 😁 )

The data passed to the templates can be found in boilingcore/template.go