typeorm table name specified more than once

Question

I have the next query:

const foundDeal: any = await dealRepository.findOne({
  where: { id: dealId },
  relations: ['negotiationPointsDeals', 'chosenInventoryToSubtractQuantity',


    'chosenInventoryToSubtractQuantity.inventoryItemType',
    'chosenInventoryToSubtractQuantity.inventoryItemType.quality', 'negotiationPointsDeals.negotiationPointsTemplate',
    'chosenInventoryToSubtractQuantity.addressOfOriginId', 'chosenInventoryToSubtractQuantity.currentLocationAddress',


    'chosenInventoryToSubtractQuantity.labAttestationDocs',
    'chosenInventoryToSubtractQuantity.labAttestationDocs.storage',

    'chosenInventoryToSubtractQuantity.proveDocuments', 'chosenInventoryToSubtractQuantity.proveDocuments.storage',
    'chosenInventoryToSubtractQuantity.inventoryItemSavedFields', 'chosenInventoryToSubtractQuantity.inventoryItemSavedFields.proveDocuments',
    'chosenInventoryToSubtractQuantity.inventoryItemSavedFields.proveDocuments.storage',


    'sellerBroker', 'sellerBroker.users',
    'seller', 'seller.users',
    'buyerBroker', 'buyerBroker.users',
    'buyer', 'buyer.users',
    'order', 'order.inventory', 'order.inventory.inventoryItemType',
    'order.inventory.inventoryItemType.quality',
    'order.inventory.addressOfOriginId', 'order.inventory.currentLocationAddress',
    'order.inventory.inventoryItemSavedFields', 'order.inventory.inventoryItemSavedFields.proveDocuments',
    'order.inventory.inventoryItemSavedFields.proveDocuments.storage',

    'order.inventory.labAttestationDocs', 'order.inventory.labAttestationDocs.storage',

    // 'postTradeProcessingDeal', 'postTradeProcessingDeal.postTradeProcessingStepsDeal',

    'order.inventory.proveDocuments',
    'order.inventory.proveDocuments.storage',
    'negotiationPointsDeals.negotiationPointsTemplate.negotiationPointsTemplateChoices',
    'postTradeProcessing',
  ],
});

So, the error is next:

error: table name "Deal__chosenInventoryToSubtractQuantity_Deal__chosenInventoryTo" specified more than once.

But I can't see any doubles in query.

Answer 1

The issue is documented in this TypeORM issue.

After some digging, I realized this error is due to TypeORM creating some kind of variable when using eager loading that is longer than Postgres limit for names.

For example, if you are eager loading products with customer, typeorm will create something along the lines of customer_products, connecting the two. If that name is longer than 63 bytes (Postgres limit) the query will crash.

Basically, it happens when your variable names are too long or there's too much nesting. Make your entity names shorter and it will work. Otherwise, you could join the tables manually using queryBuilder and assign aliases for them.

Answer 2

I ran into this issue when switching to start using the snake case naming strategy.

I think somehow the aliases that TypeORM generates by default do not collide if you "re-join" to existing eagerly-loaded relations.

However, under the new naming strategy it threw an error if I tried to add in a relation that was already eagerly loaded.

The solution for me was to find and eliminate places where I was doing relations: ["foo"] in a query where foo was already eagerly loaded by the entity.

Answer 3

It looks like you are using Nestjs, typeorm, and the snakeNamingStrategy as well, so I'll show how I fixed this with my system. I use the SnakeNamingStrategy for Typeorm which might be creating more issues as well. Instead of removing it, I extended it and wrote an overwriting function for eager-loaded aliases.

Here is my solution:

// short-snake-naming.strategy.ts

import { SnakeNamingStrategy } from "typeorm-naming-strategies";
import { NamingStrategyInterface } from "typeorm";

export class ShortSnakeNamingStrategy
  extends SnakeNamingStrategy
  implements NamingStrategyInterface
{
  eagerJoinRelationAlias(alias: string, propertyPath: string): string {
    return `${alias.replace(
      /[a-zA-Z]+(_[a-zA-Z]+)*/g,
      (w) => `${w[0]}_`
    )}_${propertyPath}`;
  }
}

// read-database.configuration.ts

import { TypeOrmModuleOptions, TypeOrmOptionsFactory } from "@nestjs/typeorm";
import { SnakeNamingStrategy } from "typeorm-naming-strategies";
import { ShortSnakeNamingStrategy } from "./short-snake-naming.strategy";

export class ReadDatabaseConfiguration implements TypeOrmOptionsFactory {
  createTypeOrmOptions(): TypeOrmModuleOptions | Promise<TypeOrmModuleOptions> {
    return {
      name: "read",
      type: "postgres",
      ...

      namingStrategy: new ShortSnakeNamingStrategy(),
    };
  }
}

The ShortSnakeNamingStrategy Class takes each eager-loaded relationship and shortens its name from Product__change_log___auth_user___roles__permissions to P_____c____a___r__permissions

So far this has generated no collisions and kept it below the 63 character max index length.

Answer 4

In my case, I have 2 solutions for resolving this problem S1: Use the relations option in the method find or findOne like

const queryResult = await this.nftRepository.find({
  where: { name: query.keyword },
  relations: ['lovedUsers', 'creator']
});

S2: If I create a query builder to make a query, I use "AS" command for another name when I am joining the same table

const queryBuilder = this.nftRepository
  .createQueryBuilder('nft')
  .leftJoinAndSelect('nft.lovedUsers', 'user')
  .leftJoinAndSelect('nft.creator', 'user as creators')
  .leftJoinAndSelect('nft.collection', 'collection')
  .leftJoinAndSelect('collection.categories', 'collection_category')
  .getMany();

Here is my table schema

@Entity()
export class NFT extends BaseEntity {
  @Column()
  name: string;

  @ManyToOne(() => User, (creator) => creator.createdNfts)
  creator: User;

  @ManyToMany(() => User)
  @JoinTable({ name: 'nft_loved_users_user' })
  lovedUsers: User[];
}

I hope it can help you.