Find rows using foreign key in TypeORM

user_5310827 0
Viewed 1525

I have an OneToMany relation from User to Message.

When I insert register a user with a message, it adds the user to the User table, and the message to the Message table with a userId pointing to the user's id.

This is done automatically using the following setup.

User entity:

@Entity()
export class User {
  @PrimaryGeneratedColumn() 
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @JoinTable()
  @OneToMany((type) => Message, (message) => message.user, {
    cascade: true,
  })
  messages: Message[];
}

Message entity:

@Entity()
export class Message {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  text: string;
  @ManyToOne((type) => User, (user) => user.messages, { eager: true })
  user: User[];
}

If I want to find all messages from a user via userId with:

const existing_msgs = await this.messageRepository.find({
    where: { userId: user.id },
});

It tells me that it cannot find the userId column, which is understandable as I did not specifically include userId to the Message entity.

But how would we query it in this case?

typeorm nestjs postgresql
2 Answers

As mentionned in This post, you have to add the decorator @JoinColumn after your manytoone. This will join the column and you will be able to perform the query you want with :

const existing_msgs = await this.messageRepository.find({
    where: { user { id: user.id }},
});

Note that this will work only on primary column like id.

And your message table will be :

@Entity()
export class Message {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  text: string;

  @ManyToOne((type) => User, (user) => user.messages, { eager: true })
  @JoinColumn() // <-- Add this
  user: User[];
}
user_111646910

I was able to do it with the following querybuilder.

        const msg_arr = await this.userRepository
          .createQueryBuilder('user')
          .leftJoinAndSelect('user.messages', 'messages')
          .where('user.id = :userId', { userId: user.id })
          .andWhere('messages.text LIKE :text', { text: message })
          .select('messages.text')
          .execute();
user_53108270
Related