Question: How to handle tables with composite primary key

[Bnaya]

I have a table, that was created outside of typeorm. It has no auto increment id, and the "ID"/primary key, is a composition of 3 fields.

Something like:

@Entity({ name: "tag_regex_to_product" })
export class tag_regex_to_product {
  @Column("varchar", {
    nullable: false,
    length: 32,
    primary: true,
    name: "product_id",
  })
  product_id!: string;

  @Column("varchar", {
    nullable: false,
    length: 20,
    primary: true,
    name: "brand_id",
  })
  brand_id!: string;

  @Column("varchar", {
    nullable: false,
    length: 100,
    primary: true,
    name: "tag_regex",
  })
  tag_regex!: string;

  @Column("int", {
    nullable: true,
    default: "1",
    name: "is_positive",
  })
  is_positive!: 1 | 0;

  @ManyToOne(() => Brand, {
    nullable: false,
    onDelete: "NO ACTION",
    onUpdate: "RESTRICT",
  })
  @JoinColumn({ name: "brand_id" })
  brand!: Brand;

  @ManyToOne(() => Product, {
    nullable: false,
    onDelete: "NO ACTION",
    onUpdate: "RESTRICT",
  })
  @JoinColumn({ name: "product_id" })
  product!: Product;
}

How can i make this setup work?

Thanks!

[Darkein]

maybe you can setup 3 params in the crud options ?

@Controller('tagRegexToProduct/:productId/:brandId/:tagRegex')
@Crud(
{
  params: {
    ...
    productId: {
      field: 'product_id',
      type: 'string'
    },
    brandId: {
      field: 'brand_id',
      type: 'string'
    },
    tagRegex: {
      field: 'tag_regex',
      type: 'string'
    },
  },
})

but i think it's a OR between the params, So you have to try

[dsbaars]

I think this is broken, I tried setting it manually using:

@Crud({
    model: {
        type: TickerPrice,
    },
    params: {
        exchange: {
            type: 'string',
            primary: true,
            field: 'exchange'
        },
        symbol: {
            type: 'string',
            primary: true,
            field: 'symbol'
        },
    }
})

But it doesn't work well, after adding a new value using the POST endpoint the select query only has the first described key in the WHERE clause:

query: SELECT `TickerPrice`.`exchange` AS `TickerPrice_exchange`, `TickerPrice`.`symbol` AS `TickerPrice_symbol`, `TickerPrice`.`info` AS `TickerPrice_info`, `TickerPrice`.`timestamp` AS `TickerPrice_timestamp`, `TickerPrice`.`datetime` AS `TickerPrice_datetime`, `TickerPrice`.`high` AS `TickerPrice_high`, `TickerPrice`.`low` AS `TickerPrice_low`, `TickerPrice`.`bid` AS `TickerPrice_bid`, `TickerPrice`.`bidVolume` AS `TickerPrice_bidVolume`, `TickerPrice`.`ask` AS `TickerPrice_ask`, `TickerPrice`.`askVolume` AS `TickerPrice_askVolume`, `TickerPrice`.`vwap` AS `TickerPrice_vwap`, `TickerPrice`.`open` AS `TickerPrice_open`, `TickerPrice`.`close` AS `TickerPrice_close`, `TickerPrice`.`last` AS `TickerPrice_last`, `TickerPrice`.`previousClose` AS `TickerPrice_previousClose`, `TickerPrice`.`change` AS `TickerPrice_change`, `TickerPrice`.`percentage` AS `TickerPrice_percentage`, `TickerPrice`.`average` AS `TickerPrice_average`, `TickerPrice`.`baseVolume` AS `TickerPrice_baseVolume`, `TickerPrice`.`quoteVolume` AS `TickerPrice_quoteVolume` FROM `ticker_price` `TickerPrice` WHERE `TickerPrice`.`exchange` = ? -- PARAMETERS: ["kraken"]
query: SELECT `TickerPrice`.`exchange` AS `TickerPrice_exchange`, `TickerPrice`.`symbol` AS `TickerPrice_symbol`, `TickerPrice`.`info` AS `TickerPrice_info`, `TickerPrice`.`timestamp` AS `TickerPrice_timestamp`, `TickerPrice`.`datetime` AS `TickerPrice_datetime`, `TickerPrice`.`high` AS `TickerPrice_high`, `TickerPrice`.`low` AS `TickerPrice_low`, `TickerPrice`.`bid` AS `TickerPrice_bid`, `TickerPrice`.`bidVolume` AS `TickerPrice_bidVolume`, `TickerPrice`.`ask` AS `TickerPrice_ask`, `TickerPrice`.`askVolume` AS `TickerPrice_askVolume`, `TickerPrice`.`vwap` AS `TickerPrice_vwap`, `TickerPrice`.`open` AS `TickerPrice_open`, `TickerPrice`.`close` AS `TickerPrice_close`, `TickerPrice`.`last` AS `TickerPrice_last`, `TickerPrice`.`previousClose` AS `TickerPrice_previousClose`, `TickerPrice`.`change` AS `TickerPrice_change`, `TickerPrice`.`percentage` AS `TickerPrice_percentage`, `TickerPrice`.`average` AS `TickerPrice_average`, `TickerPrice`.`baseVolume` AS `TickerPrice_baseVolume`, `TickerPrice`.`quoteVolume` AS `TickerPrice_quoteVolume` FROM `ticker_price` `TickerPrice` WHERE `TickerPrice`.`exchange` = ? AND `TickerPrice`.`symbol` = ? -- PARAMETERS: ["kraken","ETH/USD"]
query: START TRANSACTION
query: UPDATE `ticker_price` SET `timestamp` = ?, `high` = ?, `bid` = ?, `bidVolume` = ?, `ask` = ?, `askVolume` = ?, `vwap` = ?, `open` = ?, `close` = ?, `last` = ?, `baseVolume` = ?, `quoteVolume` = ? WHERE `exchange` = ? AND `symbol` = ? -- PARAMETERS: [1590347231414,9310,8951.8,0.01942021,8954.1,3.6,9062.51794,9175.4,8954.1,8954.1,4190.05028435,37972405.871423975,"kraken","ETH/USD"]
query: COMMIT
query: SELECT `TickerPrice`.`exchange` AS `TickerPrice_exchange`, `TickerPrice`.`symbol` AS `TickerPrice_symbol`, `TickerPrice`.`info` AS `TickerPrice_info`, `TickerPrice`.`timestamp` AS `TickerPrice_timestamp`, `TickerPrice`.`datetime` AS `TickerPrice_datetime`, `TickerPrice`.`high` AS `TickerPrice_high`, `TickerPrice`.`low` AS `TickerPrice_low`, `TickerPrice`.`bid` AS `TickerPrice_bid`, `TickerPrice`.`bidVolume` AS `TickerPrice_bidVolume`, `TickerPrice`.`ask` AS `TickerPrice_ask`, `TickerPrice`.`askVolume` AS `TickerPrice_askVolume`, `TickerPrice`.`vwap` AS `TickerPrice_vwap`, `TickerPrice`.`open` AS `TickerPrice_open`, `TickerPrice`.`close` AS `TickerPrice_close`, `TickerPrice`.`last` AS `TickerPrice_last`, `TickerPrice`.`previousClose` AS `TickerPrice_previousClose`, `TickerPrice`.`change` AS `TickerPrice_change`, `TickerPrice`.`percentage` AS `TickerPrice_percentage`, `TickerPrice`.`average` AS `TickerPrice_average`, `TickerPrice`.`baseVolume` AS `TickerPrice_baseVolume`, `TickerPrice`.`quoteVolume` AS `TickerPrice_quoteVolume` FROM `ticker_price` `TickerPrice` WHERE `TickerPrice`.`exchange` = ? -- PARAMETERS: ["kraken"]

[patrickmichalina]

Yeah this doesn't work :(