Question 328493 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does linking entites limit the ability to search and create new objects?

I work with PostgreSQL. I have three entities: Product, Warehouse and ProductOnWarehouse. Product describes the product, its price and name, Warehouse describes the warehouse, its coordinates and name, and ProductOnWarehouse describes a specific product in a specific warehouse, including a price and quantity unique to that warehouse.
They have the following form:

//Product.ts
import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm"
import ProductOnWarehouse from "./ProductOnWarehouse"

@Entity({ name: "Product" })
export default class Product {
    @PrimaryGeneratedColumn()
    id: number

    @Column({ type: "varchar", length: "80" })
    name: string

    @Column()
    price: number

    @OneToMany(
        () => ProductOnWarehouse,
        (productOnWarehouse) => productOnWarehouse.product
    )
    productOnWarehouses: ProductOnWarehouse[]
}

//Warehouse.ts
import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from "typeorm"
import ProductOnWarehouse from "./ProductOnWarehouse"

@Entity({ name: "Warehouse" })
export default class Warehouse {
    @PrimaryGeneratedColumn()
    id: number

    @Column({ type: "varchar", length: "80" })
    name: string

    @Column("real")
    lat: number

    @Column("real")
    lon: number

    @OneToMany(
        () => ProductOnWarehouse,
        (productOnWarehouse) => productOnWarehouse.warehouse
    )
    productOnWarehouses: ProductOnWarehouse[]
}

//ProductOnWarehouse.ts
import { Entity, Column, PrimaryGeneratedColumn, ManyToOne, JoinColumn } from "typeorm"
import Product from "./Product.entity"
import Warehouse from "./Warehouse.entity"

@Entity({ name: "ProductOnWarehouse" })
export default class ProductOnWarehouse {
    @PrimaryGeneratedColumn()
    id: number

    @ManyToOne(() => Product, (product) => product.productOnWarehouses)
    @JoinColumn({ name: "productId" })
    product: Product

    @ManyToOne(() => Warehouse, (warehouse) => warehouse.productOnWarehouses)
    @JoinColumn({ name: "warehouseId" })
    warehouse: Warehouse

    @Column()
    price: number

    @Column()
    amount: number
}

When I want to create or find an entry in the ProductOnWarehouse table, I come across the fact that the where condition does not require an id, but an instance of the Product and Warehouse classes:

this.storedProductsRepository.findOneBy({product: /**requires a Product instance!*/, warehouse: /**requires a Warehouse instance!*/})

the situation is similar with the creation of a new ProductOnWarehouse. Can’t I use the id, or do I have to get Product and Warehouse instances beforehand?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a solution - load relations:

    var position = await this.products.findOne({
        relations: {
            product: true,
            warehouse: true
        },
        where: {
            product: {
                id: data.productId
            },
            warehouse: {
                id: warehouseId
            }
        }
    })

  2. 0

    The where condition doesn’t require an instance of the product or warehouse, it requires an object which has all the fields of the given entity but they are all optional. So you can for example do

    this.storedProductsRepository.findOneBy({product:{id: "123"}});

    Which finds all ProductOnWarehouse entities where the product id is "123" (assuming storedProductsRepository is a repository for ProductOnWarehouse entity)

    For creating the ProductOnWarehouse entity with your current setup you need instances of a Warehouse and Product.
    If you create productId and warehouseId fields in the entity (field names based on your joinColumn names) you will be able to create a ProductOnWarehouse just from productId and warehouseId without needing to get instances of Product or Warehouse by fetching them from the db or creating them in a different way.

    I would say this is preferable, especially if you care about performance of your application because it can save you from making unnecessary queries to your DB.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search