Scopes and FK

[DaveMacNeil]

Hello, I am trying to create Models with FK. I am having trouble understanding the scope model, when to set as model: () => Customer, or just () =>. How I have my scope set up now, I get undefined errors for the fields I am trying to reference. Thanks for the help in advance.

[RobinBuschmann]

@DavidPMacNeil Hey, can you provide an example? So that I can better understand what you are trying to achieve.

[DaveMacNeil]

Hey @RobinBuschmann, Thank you for the quick reply. example.txt

I've uploaded an example (in txt) because it is over a couple of files. In this example, Equipment will not be created, but some other tables will be. I have one table being created with a FK right now with the same syntax.

[DaveMacNeil]

So I've found out that I can create the Equipment table by removing one of the FK constraints. Am I not properly formatting the constraints, or will sequelize-typescript only allow 1 FK per model definition for now?

[RobinBuschmann]

@DavidPMacNeil I recommend you to read the docs, so that you fully understand what scopes are about:

Scoping allows you to define commonly used queries that you can easily use later. Scopes can include all the same attributes as regular finders, where, include, limit etc.

http://docs.sequelizejs.com/manual/tutorial/scopes.html

I've found some issues in your example:

• In Equipment.ts you included Equipment in your scope, despite of Equipment has no relation to itself:

  @Scopes({
  full: {
  include: [{
    model: () => Department
  },
  {
    model: () => Location
  },
    () => Equipment // <-- remove this
  ]
  }
  })
  @Table
  class Equipment extends Model<Equipment> { ... }

  Same for Location.ts, Department.ts

• If you define your foreign key columns explicitly like you did, you don’t need to refer them in @BelongsTo (or other relation decorators), but decorate these foreign keys explicitly as foreign keys:

  
  @BelongsTo(() => Department) // <--- 'DepartmentID' parameter removed
  departments: Department; // typo? departments -> department

@ForeignKey(() => Department) // <--- foreign key annotation @Column(DataType.STRING(50)) DepartmentID: string;

- In Department.ts I see a misuse of foreign key: `DepartmentID` is the primary key, but not the foreign key of Equipment.
- In Department.ts there is no relation to equipment defined, but you included `Equipment` in the scope of `Department`
- in Location.ts there is no relation to equipment defined, but you included `Equipment` in the scope of `Location`
- You can use a shortcut for `include: [{model: () => Model}]`:  `include: [() => Model]`

Can you provide me the exact relations you want to define for your models (e.g. `User 1:n Follower` -> one-to-many)? With this information I can show you a working example :) 

[DaveMacNeil]

OK I see. I wasn't sure from the example listed in your repo. I would like Department 1:n Equipment, Location 1:n Equipment ,Location 1:1 Customer `. Thanks for the help!

[RobinBuschmann]

@DavidPMacNeil I'm not really sure about your model architecture, but here are the definitions of what you described:

Department.ts

@Table
export class Department extends Model<Department> {

  @Column({
    primaryKey: true,
    allowNull: false,
    type: DataType.STRING(25)
  })
  departmentId: string;

  @Column
  description: string;

  @HasMany(() => Equipment)
  equipments: Equipment[];
}

Equipment.ts

@Table
export class Equipment extends Model<Equipment> {

  @Column({
    primaryKey: true,
    allowNull: false,
    type: DataType.STRING(25)
  })
  equipmentId: string;

  @ForeignKey(() => Department)
  @Column(DataType.STRING(25))
  departmentId: string;

  @ForeignKey(() => Location)
  @Column(DataType.STRING(25))
  locationId: string;

  @BelongsTo(() => Department)
  department: Department;

  @BelongsTo(() => Location)
  location: Location;
}

Customer.ts

@Table
export class Customer extends Model<Customer> {

  @Column({
    primaryKey: true,
    allowNull: false,
    type: DataType.STRING(25)
  })
  customerId: string;

  @HasOne(() => Location)
  location: Location;
}

Location.ts

@Table
export class Location extends Model<Location> {

  @Column({
    primaryKey: true,
    allowNull: false,
    type: DataType.STRING(25)
  })
  locationId: string;

  @Column
  address: string;

  @ForeignKey(() => Customer)
  @Column
  customerId: string;

  @HasMany(() => Equipment)
  equipments: Equipment[];

  @BelongsTo(() => Customer)
  customer: Customer;
}

• The one-to-one relation between Location and Customer can also be designed in another way. It depends on where the foreign key should exist - on Location or Customer. In my example the foreign key exists on Location.
• Regarding the primary keys. If I understand your example correctly, the primary keys don't meant to be autoincrement, but handled manually, right?
• When defining your object relations, scopes aren't needed. This is only if you want to encapsulate some common used query configuration.

If you still have questions, don't hesitate to ask ;)

[DaveMacNeil]

Thanks a lot for the help! Yes for the architecture, I am trying to say that One Department can have lots of equipment, Equipment belongs to a department, and belongs to a location, and That a customer has a location, and that a location belongs to a customer so that all looks good to me!