GORM preload: How to use a custom table name

Question

I have a GORM query with a preload that works just fine because I'm binding it to a struct called "companies" which is also the name of the corresponding database table:

var companies []Company
db.Preload("Subsidiaries").Joins("LEFT JOIN company_prod ON company_products.company_id = companies.id").Where("company_products.product_id = ?", ID).Find(&companies)

Now I want to do something similar, but bind the result to a struct that does not have a name that refers to the "companies" table:

var companiesFull []CompanyFull
db.Preload("Subsidiaries").Joins("LEFT JOIN company_prod ON company_products.company_id = companies.id").Where("company_products.product_id = ?", ID).Find(&companies)

I've simplified the second call for better understanding, the real call has more JOINs and returns more data, so it can't be bound to the "companies" struct.

I'm getting an error though:

column company_subsidiaries.company_full_id does not exist

The corresponding SQL query:

SELECT * FROM "company_subsidiaries" WHERE "company_subsidiaries"."company_full_id" IN (2,1)

There is no "company_subsidiaries.company_full_id", the correct query should be:

SELECT * FROM "company_subsidiaries" WHERE "company_subsidiaries"."company_id" IN (2,1)

The condition obviously gets generated from the name of the struct the result is being bound to. Is there any way to specify a custom name for this case?

I'm aware of the Tabler interface technique, however it doesn't work for Preload I believe (tried it, it changes the table name of the main query, but not the preload).

Updated: More info about the DB schema and structs

DB schema

TABLE companies
ID Primary key
OTHER FIELDS

TABLE products
ID Primary key
OTHER FIELDS 

TABLE subsidiaries
ID Primary key
OTHER FIELDS

TABLE company_products
ID Primary key
Company_id Foreign key (companies.id)
Product_id Foreign key (products.id)

TABLE company_subsidiaries
ID Primary key
Company_id Foreign key (companies.id)
Subsidiary_id Foreign key (subsidiaries.id)

Structs

type Company struct {
    Products     []*Product `json:"products" gorm:"many2many:company_products;"`
    ID           int        `json:"ID,omitempty"`
}

type CompanyFull struct {
    Products     []*Product `json:"products" gorm:"many2many:company_products;"`
    Subsidiaries []*Subsidiary `json:"subsidiaries" gorm:"many2many:company_products;"`
    ID           int        `json:"ID,omitempty"`
}

type Product struct {
    Name     string `json:"name"`
    ID       int    `json:"ID,omitempty"`
}

type Subsidiary struct {
    Name     string `json:"name"`
    ID       int    `json:"ID,omitempty"`
}

Generated SQL (by GORM)

SELECT * FROM "company_subsidiaries" WHERE "company_subsidiaries"."company_full_id" IN (2,1)

SELECT * FROM "subsidiaries" WHERE "subsidiaries"."id" IN (NULL)

SELECT companies.*, company_products.*, FROM "companies" LEFT JOIN company_products ON company_products.company_id = companies.id WHERE company_products.product_id = 1

could you please provide a little more info about your DB schema (it doesn't have to be the full schema)? I really can't follow how the queries are being generated given the provided info. Also, a bit more info on the structures would be much appreciated. — Pablo Flores
Just did. Thanks a lot for pointing out the missing parts! I did not want to confuse too much, but provided not enough info apparently. — user3255061

Pablo Flores Pablo Flores · Accepted Answer · 2021-03-13T21:07:08

Seems like the way to go in this case may be to customize the relationship in your CompanyFull model. Using joinForeignKey the following code works.

type CompanyFull struct {
    Products     []*Product    `json:"products" gorm:"many2many:company_products;joinForeignKey:ID"`
    Subsidiaries []*Subsidiary `json:"subsidiaries" gorm:"many2many:company_subsidiaries;joinForeignKey:ID"`
    ID           int           `json:"ID,omitempty"`
}

func (CompanyFull) TableName() string {
    return "companies"
}

func main(){
...
  result := db.Preload("Subsidiaries").Joins("LEFT JOIN company_products ON company_products.company_id = companies.id").Where("company_products.product_id = ?", ID).Find(&companies)
  if result.Error != nil {
    log.Println(result.Error)
  } else {
    log.Printf("%#v", companies)
  }

For more info regarding customizing the foreign keys used in relationships, take a look at the docs https://gorm.io/docs/many_to_many.html#Override-Foreign-Key

GORM preload: How to use a custom table name

1 Answers