Validation multi-company mysql relationship

Question

Problem: a company can associate records that are not hers, just informing the id in the relationship

Setting: My API is multi-company and each company manages its records (products, orders ...), but these companies can only view and use records that belong to them. However, it is possible to insert an id of another company’s product into an order and should not

Example: The Apple company can only inform the iPhone product on your orders, in case you try to inform the Google Pixel should appear some error message

Remarks: I hope I don’t need to validate each record associated with the request before saving it, as my actual schema has multiple relationships (20 tables associated with a request, about 2 cascading levels) and this is just a small example. It would be very complicated to search the database for each product and check if it belongs to the company. I think it would be possible to avoid this recording with some index setting

Question: How can I avoid associating other companies' records with the company’s current records, even by informing the id in the body of the request?

I appreciate your help, because I have a system in production with this situation.

company.entityts.

@Entity()
export class Company {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Product, (product) => product.company)
  products: Product[];

  @OneToMany(() => Order, (order) => order.company)
  orders: Order[];
}

product.entityts.

@Entity()
export class Product {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  description: string;

  @ManyToOne(() => Company, (company) => company.products)
  company: Company;
}

order.entityts.

@Entity()
export class Order {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  description: string;

  @ManyToOne(() => Company, (company) => company.orders)
  company: Company;

  @ManyToMany(() => Product)
  @JoinTable()
  products: Product[];
}

order.controller.ts

@Controller('orders')
export class OrderController {

  constructor(
    @InjectRepository(Order)
    public repository: Repository<Order>,
  ) { }

  @Post()
  public async create(@Body() body: Order): Promise<Order> {
    body.id = undefined;
    body = await this.repository.save(body);
    return await this.repository.findOne(body.id, { relations: ['products'] });
  }

  @Put(':id')
  public async update(@Param('id') id, @Body() body: Order): Promise<Order> {
    body.id = Number(id);
    await this.repository.save(body);
    return await this.repository.findOne(id, { relations: ['products'] });
  }
}

Schedule

• id: 1 Description: Apple
• id: 2 Description: Google

Product Table

• id: 1 Description: iPhone companyId: 1
• id: 2 Description: Google Pixel companyId: 2

POST http://localhost:3040/Orders Body Request: Obs.: here the user informs the iPhone that is a product he knows, and forces the product with id 2, and this is an unknown product, because api only returns what products are visible to Apple, in this case only iPhone

{
    "company": 1,
    "description": "Order 001",
    "products": [
        {
            "id": 1,
            "description": "iPhone"
        },
        {
            "id": 2
        }
    ]
}

Body Response: Note: Even when informing the product of another company, in the return of the api is presented the Google Pixel that could not be registered in the Apple order. Here you must have an error message

{
    "id": 2,
    "description": "Order 001",
    "products": [
        {
            "id": 1,
            "description": "iPhone"
        },
        {
            "id": 2,
            "description": "Google Pixel"
        }
    ]
}

Results

Order Table

Product Table of the Order

Notes:

1. The solution to validate whether the order can be saved based on the constant of the company ID the user is logged in to, only works for the first level, in this case the order. The issue is that the related products in the order, may not come with the informed business field, only with the ID
2. I really don’t see how an elegant and reusable solution can consult product by product in the database to see if it belongs to the company or not, I believe mysql itself could have some form of validating by the foreign key if the record can be associated with no at the time of recording by the ORM