Handling i18n, hidding translation table in results

[alexandre-dos-reis]

Consider the following schema, I would like to implement i18n.

class ProductTable extends BaseTable {
  readonly table = 'product';
  columns = this.setColumns((t) => ({
    id: t.uuid().primaryKey(),
    stock: t.integer().min(0).default(0),
    price: t.integer().min(0).default(0),
  }));

  relations = {
    productTranslations: this.hasMany(() => ProductTranslationTable, {
      primaryKey: 'id',
      foreignKey: 'parentModelId',
    }),
  };
}

class ProductTranslationTable extends BaseTable {
  readonly table = "productTranslation";
  columns = this.setColumns((t) => ({
    parentModelId: t.uuid().foreignKey("product", "id", {
      onDelete: "CASCADE",
    }),
    languageCode: t.enum("LangCode", ["FR", "EN", "JP"]),
    name: t.varchar(255),
    description: t.varchar(255),
    ...t.primaryKey(["parentModelId", "languageCode"]),
  }));

  relations = {
    product: this.belongsTo(() => ProductTable, {
      primaryKey: "id",
      foreignKey: "parentModelId",
    }),
  };
}

I would like to obtain the following result and hide the join to the ProductTranslationTable :

  {
    id: "9d3fe83a-fe33-4f59-a06f-b32bd2573d26",
    stock: 10,
    price: 749,
    name: "Table à manger industrielle en bois d'acacia et métal noir",
    description: "<div data-v-cd98ad86=\"\" data-el=\"product-description-panel__rich-text\" class=\"mt-1\"><p>Retrouvez l'authenticité du bois à travers la <strong>table à manger indus en acacia et métal noir PALISSANDRE</strong> !&nbsp;</p><ul><li>Sa particularité ? Un plateau à l'état brut qui fait la part belle aux aspérités du bois.</li><li>Fixée sur deux pieds modernes en métal noir, cette <strong>table à manger 8/10 personnes</strong> conjugue les styles avec goût.</li><li>Son plateau en acacia lui assure une bonne robustesse.</li><li>Nouvelle mission : dénichez les chaises parfaites qui l'accompagneront !</li></ul></div>"
  },

I found a way to do it :

  const productsFr = await db.product
    .join("productTranslations")
    .select("id", "stock", "price", "productTranslations.*")
    .where({ "productTranslations.languageCode": "FR" })
    .transform((r) =>
      r.map((x) => {
        const { productTranslations, ...rest } = x;
        return {
          ...rest,
          name: x.productTranslations.name,
          description: x.productTranslations.description,
        };
      }),
    );

Is there a better way to do it? more scalable ? using the repository pattern ? Thanks for future advices.

[alexandre-dos-reis]

One way is to simply call all the fields that I need but I must specify them, it is not very scalable :

  const productsFR = await db.product
    .join("productTranslations")
    .select(
      "id",
      "stock",
      "price",
      "productTranslations.name",
      "productTranslations.description",
    )
    .where({ "productTranslations.languageCode": "FR" });

[romeerez]

Sorry for the late reply, this seemed like a tough question so I put it off while looking at other issues. But actually, it's how it's designed and how it works, and I don't see what can be improved here.

When selecting a joined table with a star, the star means to join select columns, and I think it's intuitive that the joined table is nested. And this allows to avoid possible conflicts, if productTranslations also have id, it will be nested.

.select("id", "stock", "price", "productTranslations.*")

You have used the transform to change the shape. In the second message, you're listing the columns explicitly so the resulting records are flat.

I think that it's good as it is, and adding some smart automatic column destructuring while technically is possible, but will only harm readability.

  const productsFR = await db.product
    .join("productTranslations")
    .select(
      "id",
      "stock",
      "price",
      "productTranslations.name",
      "productTranslations.description",
    )
    .where({ "productTranslations.languageCode": "FR" });

When I see this code I can instantly tell what columns are selected from which tables, and what the result will be.

Let's imagine there is a ...joinedTable syntax:

const productsFR = await db.product
    .join("productTranslations")
    .select(
      "id",
      "stock",
      "price",
      "...productTranslations",
    )
    .where({ "productTranslations.languageCode": "FR" });

• we can't see what columns we are selected
• when we add more columns to productTranslations, they all will be selected, no matter if we want them or not
• if we drop the description column, when listing columns explicitly TS will show an error preventing possible mistakes
• productTranslations may have its own id, and product has id, TS won't catch this, and the database also will select this without problems, but we will have only one id in the result

To make it a bit more compact, you can rename the relation (table name remains the same, only the relation) to translations. The where condition can be placed to the join and table name may be omitted in there:

const productsFR = await db.product
    .join("translations", (q) => q.where({ languageCode: 'FR' }))
    .select(
      "id",
      "stock",
      "price",
      "translations.name",
      "translations.description",
    )