Postgres, Relationship and Composite key

[scal-lider]

I'm trying to model the existing Postgres database through Lucid, and that database has some tables with composite primary keys. I know Lucid does not handle composite keys for now so I'm loading/creating manualy.

My solution

• I didn't use relationships
• To create the rows, I'm override the createMany static function, using Database queries directly, return all its columns and then creating the model instance.
• to fetch the rows I crated a spefic load function

My problems with that:

1. Postgres provider (pg) needs to return a column id, which does not exist, that why I using Database directly
2. Missing the preload from relations to fetch more than one related table data

My Question

There is a way to use relationships?
If not, how can I help create a way?

The Code example

On the Owner Model

componentes?: PedidoItemComponente[];
async loadComponentes(): Promise<void> {
  this.componentes = await PedidoItemComponente.findByPedidoEOrdemDoItem(
    this.idPedido,
    this.ordemNoPedido
  );
}

On The Owned Model

  static async createMany(
    componentes: Partial<PedidoItemComponente>[],
    dbTransaction?: TransactionClientContract
  ) {
    const tabelaComponente = Database.table(PedidoItemComponente.table);
    if (dbTransaction) tabelaComponente.useTransaction(dbTransaction);

    const rows = await tabelaComponente
      .multiInsert(
        componentes.map((componente, index) => ({
          pit_cd_pedido: componente.idPedido,
          pit_cd_item: componente.ordemDoItemNoPedido,
          pit_cd_ordem: componente.ordemDoCompenenteNoItem ?? index + 1,
          pit_cd_produto: componente.idProduto,
          pit_vl_quantidade: componente.fracao,
          pit_vl_valor_unitario: componente.valorUnitario,
          pit_vl_total: componente.total,
          pit_ds_fracao: componente.fracaoComoString,
        }))
      )
      .returning([
        'pit_cd_pedido',
        'pit_cd_item',
        'pit_cd_ordem',
        'pit_cd_produto',
        'pit_vl_quantidade',
        'pit_vl_valor_unitario',
        'pit_vl_total',
        'pit_ds_fracao',
      ])
      .exec();

    return rows.map(mapeiaLinhaDaTabelaParaInstancia);
  }

  static async findByPedidoEOrdemDoItem(
    idPedido: IdPedido,
    ordemDoItemNoPedido: number
  ): Promise<PedidoItemComponente[]> {
    const result = await PedidoItemComponente.query()
      .where('idPedido', idPedido)
      .where('ordemDoItemNoPedido', ordemDoItemNoPedido)
      .exec();
    return result;
  }

this function findByPedidoEOrdemDoItem returns a list of components of an item in an order(pedido).

The primary key would be

• for owner table ['pit_cd_pedido', 'pit_cd_item']
• for owned table ['pit_cd_pedido', 'pit_cd_item', 'pit_cd_ordem']

[scal-lider]

Another example why I need composite keys:

I have 2 tables that relates to a third table using the same columns

The third table:

id	foreing_key	foreing_key_type
1	20	"P"
2	20	"S"

The product sale table:

id	code	price
20	111111111	65.89

The service order table:

id	name	executor_id
20	service name	1

[scal-lider]

Hi people,

Since I want to help with this, I started looking at the lucid src.
Then I saw that the primary key is everywhere (which makes sense for a relational database), so changing it to accept an array would take more time than I have now, but maybe soon xD

Meanwhile I found a function BaseModel.$getQueryFor

    const primaryKeyColumn = modelConstructor.$keys.attributesToColumns.get(
      modelConstructor.primaryKey,
      modelConstructor.primaryKey
    )
    
   /**
     * Returning insert query for the inserts
     */    
   if (action === 'insert') {
      const insertQuery = client.insertQuery().table(modelConstructor.table)
      insertQuery.returning(primaryKeyColumn)
      return insertQuery    
  }

And it made me reflect on:

• So is not pg that only returns the id, is the BaseModel?
  • Or it is there to make all clients behave the same ?
• It is possible to return all columns? (using modelConstructor.$keys.attributesToColumns.all())
  • if yes, it would break things?

I still wouln'd be able to use Relationships, but is I'll be able to hide the Database call in a custom adapater.

[thetutlage]

Composite keys is not Lucid does not support and requires a lot of internal changes to make it work properly, especially with relationships. I personally do not have the mental bandwidth right now to even go through every single scenario and make sure it works fine with composite keys. So, I will prefer to skip this altogether for now.

[scal-lider]

As Virk says, Lucid is not handling composite keys any time soon, so lets discuss how to handle it using Lucid without relationships:

What you think is the best way of doing that?

• using a custom adapter to return all coluns?
• override create and createMany?
• How to handle find?

I stiil need to test around to make my opinion.

PS: @thetutlage please lets us know when you start thinking about this. Maybe by then I can help 😉

[scal-lider]

Hi people,

So I think I solve a part of my issues by extends BaseModel and overriding the $getQueryFor and creating a list of composites keys:

export class MyBaseModel extends BaseModel {
  protected static compositeKeys: string[] = [];

  public $compositeKeyValue(this): any | undefined {
    const modelConstructor = this.constructor as typeof MyBaseModel;
    const getValue = (key) => {
      const column = modelConstructor.$getColumn(key);
      if (column && column.hasGetter) {
        return this[key];
      }
      return this.$getAttribute(key);
    };
    return modelConstructor.compositeKeys.reduce((obj, key) => {
      obj[key] = getValue(key);
      return obj;
    }, {});
  }

  $getQueryFor(
    action: "insert" | "update" | "delete" | "refresh", 
    client: QueryClientContract): any 
   {
    const modelConstructor = this.constructor as typeof MyBaseModel;
    const primaryKeysColumns = modelConstructor.compositeKeys.map((key) =>
      modelConstructor.$keys.attributesToColumns.resolve(key)
    );
    
    /** Returning insert query for the inserts  */
    if (action === "insert") {
      const insertQuery = client.insertQuery()
        .table(modelConstructor.table)
        .returning(primaryKeysColumns);
      return insertQuery;
    }

    /** Returning generic query builder for rest of the queries    */
    const query = client.modelQuery(modelConstructor);
    const compositeKeysValues = this.$compositeKeyValue();
    for (const key in compositeKeysValues) {
      const column = modelConstructor.$keys.attributesToColumns.resolve(key);
      query.where(column, compositeKeysValues[key]);
    }
    return query;
  }
}

With this I can create the relationship between Pedido and PedidoItem without throwing the missing "id" coulmn error and it add all the columns in the compositeKeys array in the generics querys.

I could make compositeKeys concatenate the primary key and I think that would make my code work whether the table uses a primary key or not.
But since a table either uses a composite key or a primary key, I choose to use one BaseModel or the other, and keep my code as close as possible to the Lucid.

[scal-lider]

I also create a method to fetch models by passing a search object to help fetching throw multiples columns

public static fetchBy<T extends typeof MyBaseModel>(
    this: T,
    searchPayload: Partial<ModelAttributes<InstanceType<T>>>,
    options?: ModelAdapterOptions
  ): Promise<InstanceType<T>[]> {
    const query = this.query(options);
    for (const [key, value] of Object.entries(searchPayload)) {
      query.where(key, value as any);
    }
    return query;
  }
  
  public static fetchOne<T extends typeof MyBaseModel>(
    this: T,
    searchPayload: Partial<ModelAttributes<InstanceType<T>>>,
    options?: ModelAdapterOptions
  ): Promise<InstanceType<T> | null> {
    return this.fetchBy(searchPayload, options).first();
  }

[frogzop]

Using primary key(id, store_id) is a bad idea. This will make many queries more complicated and more prone to error. It sounds like what you are really trying to make is a many-to-many relationship between stores and offers. If this is the case you should have a store table with unique store_id as a primary key, an offer table with unique offer_id as a primary key and a store_offer table that has a primary key of store_id and offer_id.. .