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

Postgresql – NestJS TypeORM How to have a ManyToOne relationship member which type is conditionnal?

I have an entity Message that has a sender (User) and a receiver, but the receiver can either be a User or a Channel (both entities have the messagesIn member), and I want to implement this using TypeORM relationships.

Would It be possible to do something like this with TypeORM(postgres)/NestJS (Or is there any conventional other way to achieve the same goal) ?

// message.entity.ts

@Entity()
export class Message {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  content: string;

  @ManyToOne(() => User, (snd) => snd.messagesOut)
  sender: User;

  // That line doesn't compile
  @ManyToOne(() => User | Channel, (rcv) => rcv.messagesIn)
  receiver: User | Channel;
};
Tags:

2

Answers


  1. 0

    You cannot have a column which is a foreign key either of multiple tables; a FK must always reference a specific column/column set in a single table. You handle this situation by defining a nullable column(s) referencing each table, then place a constraint requiring one of them to be null. Unfortunately I am not familiar enough with your obscurification manager (TypeORM) so I will also provide a raw Postgres table definition.

    Create table Message( id          uuid default gen_random_uuid()
                                  primary key
                    , content     text 
                    , sender_user uuid
                                  references users(id) 
                    , rcv_user    uuid 
                                  references users(id) 
                    , rcv_channel uuid 
                                  references channels(id)                   
                    , constraint  only_user_or_channel_ck 
                                  check ( num_nulls(rcv_user,rcv_channel) = 1 )
                    );

    Of course that makes assumptions about you tables, their keys and column types.

    Login or Signup to reply.
  2. 0

    What you’re trying to do isn’t possible because TypeORM needs to know exactly what entity you are trying to populate so then it can query in the right place.

    TypeORM has some mechanisms that allows you to create a single table that all the records, both from User and Channel, will be inserted in, see more here.

    But, if you want to have two dedicates table for each entity (aka Table Per Type Inheritance), I would suggest create another entity called Receiver that contains common properties, that are related to the messaging mechanism, between User and Channel, plus a property called type, that will define if the Receiver is a User or a Channel; and then reference the Receiver entity in each one of the two tables.

    export enum ReceiverType {
  USER = 'USER',
  CHANNEL = 'CHANNEL',
}

@Entity()
export class Receiver {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  // all common properties that you want related to messaging goes here

  @OneToMany(() => Message, (msg) => msg.receiver)
  messagesIn: Message[];

  @Column({
    type: "enum",
    enum: ReceiverType,
  })
  type: ReceiverType
}

    @Entity()
export class User {
  // user properties...

  @OneToOne(() => Receiver)
  @JoinColumn()
  receiver: Receiver
}

    @Entity()
export class Channel {
  // channel properties...

  @OneToOne(() => Receiver)
  @JoinColumn()
  receiver: Receiver
}

    Then, in the Message entity, you will be able to reference Receiver:

    @Entity()
export class Message {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  content: string;

  @ManyToOne(() => User, (snd) => snd.messagesOut)
  sender: User;

  @ManyToOne(() => Receiver, (rcv) => rcv.messagesIn)
  receiver: Receiver;
};

    So, when you get a Message entity through the repository, you will receive something like:

    {
  id: string;
  content: string;
  sender: { ... };
  receiver: {
    // common properties you defined...
    messagesIn: [ ... ],
    type: ReceiverType,
  }
}

    With this information in hands, you will be able to access the data that you want and is necessary for the message sending flow, and, if you need some specific information that is only present in the User or Channel entity, you can get the type from Receiver and retrieve the respective entity by the receiver id, something like:

    if (message.receiver.type === ReceiverType.USER) {
  const user = await userRepository.findOne({
    where: {
      receiver: { id: message.receiver.id },
    },
  });

  ...
}

if (message.receiver.type === ReceiverType.CHANNEL) {
  const user = await channelRepository.findOne({
    where: {
      receiver: { id: message.receiver.id },
    },
  });

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

Back To Top
Search