My findings indicate that the solution is not obvious. Let me provide a brief example of what I aim to achieve.
We have 3 tables: Company, Product, Lookup...
The Company table has multiple Products associated with it while each Product belongs to one Company. The Lookup table acts as an enum/constant for fixed values such as state/country names, specific naming conventions for applications, etc.
Below are the sequelize-typescript models (although the query pertains to sequelize js as well):
// COMPANY
@Table
export default class Company extends Model<Company> {
@PrimaryKey
@Column
Oid:number;
@Column
Name:string;
@Column
Address:string;
@HasMany(() => Product)
products: Product[];
}
// PRODUCT
@Table
export default class Product extends Model<Product>{
@PrimaryKey
@Column
Oid: number;
@ForeignKey(() => Company)
@Column
companyOid: number;
@BelongsTo(() => Company)
company: Company;
@Column
Price: number;
@Column
Name: string;
//@ForeignKey(() => Lookup) //attempt #1
//@Column({references: {model: "Lookup", key: "Oid"}}) //attempt #2
@Column
LkpStateOid: number;
}
// LOOKUP
@Table
export default class Lookup extends Model<Lookup> {
@PrimaryKey
@Column
Oid:number;
@Column
Value:number;
}
The challenge lies in establishing a solid relationship between Product.LkpStateOid and Lookup.Oid, where one references the other to retrieve the Lookup.value. While this is a straightforward join in SQL databases, Sequelize requires predefined relationships between tables in order to fetch associated data in a query. My goal is to execute the following:
const companyInfo = await Db.Company.find({
include: [{
model: Product,
include: [{
model: Lookup,
attributes: ["value"]
}]
}]
})
The initial include works fine, but the nested include poses a challenge. Despite trying various annotations on the table, the primary concern remains that I do not wish to hardcode the association between the Lookup table and a specific table like Product, as Lookup could contain essential values needed by numerous tables. Is there a way to overcome this hurdle?
You may find this resource useful: Any assistance is highly appreciated. Thank you!