CODEWITHSUNDEEP
gogo-gorm
anonymous-dev
anonymous-dev

Reputation: 3529

How should I reference two tables with a single foreign key?

I have a Company model and I have a Customer model. Both can be a relation.

type Customer struct {
    gorm.Model
    Title string
}

type Company struct {
    gorm.Model
    Title string
}

type Relation struct {
    gorm.Model
    CustomerOrCompanyID uint
}

So both are a relation. How can I have my relation pointing back to either the company or customer?

Upvotes: 5

Views: 1687

Answers (2)

Ezequiel Muns
Ezequiel Muns

Reputation: 7762

There is a way to do this naturally in Gorm using a polymorphic Has One relationship. This will enable you to easily run some of the queries that @TheSphinX has provided.

You will however need to add a type discriminator column to your relation table in order to make it work:

type Customer struct {
    gorm.Model
    Title string
    Rel   Relation `gorm:"polymorphic:Owner"`
}

type Company struct {
    gorm.Model
    Title string
    Rel   Relation `gorm:"polymorphic:Owner"`
}

type Relation struct {
    gorm.Model
    Foo       string
    OwnerID   uint
    OwnerType string
}

Now you create records normally:

cust := Customer{
    Title: "Cust",
    Rel:   Relation{Foo: "bar"},
}
comp := Company{
    Title: "Comp",
    Rel:   Relation{Foo: "baz"},
}
db.Create(&cust)
db.Create(&comp)

To run a query that gets either Customer or Company with the related information as in @TehSphinX's first two queries you'd do something like:

var cust Customer
db.Joins("Rel").First(&cust, 1)

The last query would be more complex, but can also be done with a custom row struct and joins:

var rows []struct {
    Relation
    Customer Customer `gorm:"embedded;embeddedPrefix:cust_"`
    Company  Company  `gorm:"embedded;embeddedPrefix:comp_"`
}

db.Select(`
        relations.*,
        customers.id AS cust_id,
        customers.title AS cust_title,
        companies.id AS comp_id,
        companies.title AS comp_title
    `).
    Model(&Relation{}).
    Joins("LEFT JOIN customers ON relations.owner_id = customers.id AND relations.owner_type = 'customers'").
    Joins("LEFT JOIN companies ON relations.owner_id = companies.id AND relations.owner_type = 'companies'").
    Find(&rows)

// now rows[i].Relation is filled, and rows[i].Customer or rows[i].Company 
// are non-zero depending on rows[i].Relation.OwnerType

Upvotes: 7

TehSphinX
TehSphinX

Reputation: 7440

Note: speaking from a pure SQL point of perspective, since I haven't worked with gorm yet:

In my comment above I asked:

How would you know if the ID in CustomerOrCompanyID is a customer ID or a company ID? Do you have some boolean field saying this is a customer or not? Or does the ID itself contain that information? Like cust-1234 and comp-1234?

Since you need a way to distinguish what kind of ID is in CustomerOrCompanyID anyway, I'd suggest to have two fields: CustomerID and CompanyID. One of them is always 0 (or NULL).

That way you know which table to join in and can also join in both at the same time depending on what data type each row has, the columns for the customer or the company will be filled.

I think gorm should not have a problem anymore if there are 2 fields as it would handle each of them as a normal relation that is not set for all columns.

Some example SQL statements (MySQL Dialekt):

All customers:

SELECT 
    Relation.ID as RelID, Relation.OtherColumn,
    Customer.ID, Customer.Name
    /* etc. */
FROM 
    Relation INNER JOIN Customer ON (Relation.CustomerID=Customer.ID);

All companies:

SELECT 
    Relation.ID as RelID, Relation.OtherColumn, 
    Company.ID, Company.Name
    /* etc. */
FROM 
    Relation INNER JOIN Company ON (Relation.CompanyID=Company.ID);

You might also want all data in Relation with the columns of Company and Customer and decide in the frontend which ones to use:

SELECT 
    Relation.ID as RelID, 
    Relation.OtherColumn, 
    Customer.ID as CustID,
    Customer.Name as CustName,
    Company.ID as CompID,
    Company.Name as CompName,
    /* etc. */
FROM 
    Relation 
    LEFT JOIN Company ON (Relation.CompanyID=Company.ID)
    LEFT JOIN Customer ON (Relation.CustomerID=Customer.ID);

Upvotes: 1

Related Questions