How to get TypeORM's ViewEntity work with relations

Question

I have the following entities:

• MovieMetadata - defines the generic data for a movie

• MovieTranslations - a many-to-one to the MovieMetadata and includes columns to hold multiple translations for a single movie

• Genre - a one-to-many to a MovieMetadata

I also have a Movie ViewEntity that joins the two tables together.

My goal is to have the genre relation in the ViewEntity.

Entities schema

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

  @Column({ default: '1970-01-01 00:00:00', type: 'datetime' })
  releaseDate: Date;

  @Column()
  length: number;

  @OneToMany(() => MovieTranslation, (movieTranslation) => movieTranslation.movieMetadata, { cascade: true })
  translations: MovieTranslation[];

  @ManyToOne(() => Genre, (genre) => genre.movies)
  genre: Genre;
}

@Entity()
export class MovieTranslation {
  @PrimaryColumn()
  movieId: number;

  @PrimaryColumn()
  languageCode: string;

  @ManyToOne(() => MovieMetadata)
  @JoinColumn({ name: 'movieId' })
  movieMetadata: MovieMetadata;

  @Column()
  title: string;

  @Column({ length: Movie.STORYLINE_MAX_LENGTH })
  storyline: string;

  @Column({ length: 3000 })
  posterUrl: string;

  @Column({ length: 3000 })
  trailerUrl: string;
}

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

  @Column({ type: 'json' })
  nameMultilingual: { [key: string]: string };

  @OneToMany(() => MovieMetadata, (movieMetadata) => movieMetadata.genre)
  movies: MovieMetadata[];
}

@ViewEntity({
  expression: (dataSource: DataSource) =>
    dataSource
      .createQueryBuilder()

      .select('movie.id', 'id')
      .addSelect('movie.releaseDate', 'releaseDate')
      .addSelect('movie.length', 'length')
      .addSelect('movieTranslation.languageCode', 'languageCode')
      .addSelect('movieTranslation.title', 'title')
      .addSelect('movieTranslation.storyline', 'storyline')
      .addSelect('movieTranslation.posterUrl', 'posterUrl')
      .addSelect('movieTranslation.trailerUrl', 'trailerUrl')
      .addSelect('movie.genre', 'genreId')

      .from(MovieMetadata, 'movie')
      .leftJoin(MovieTranslation, 'movieTranslation', 'movieTranslation.movieId = movie.id'),
})
export class Movie {
  static readonly DEFAULT_LANGUAGE_CODE = 'en';
  static readonly STORYLINE_MAX_LENGTH = 765;

  @ViewColumn()
  id: number;

  @ViewColumn()
  languageCode: string;

  @ViewColumn()
  releaseDate: Date;

  @ViewColumn()
  length: number;

  @ViewColumn()
  title: string;

  @ViewColumn()
  storyline: string;

  @ViewColumn()
  posterUrl: string;

  @ViewColumn()
  trailerUrl: string;

  @ManyToOne(() => Genre, (genre) => genre.movies)
  @JoinColumn({ name: 'genreId' })
  genre: Genre;
}

I tried to get a list of movies with the genre like this:

const [movies, totalCount] = await this.movieRepository.findAndCount({
  select: {
    id: true,
    title: true,
    posterUrl: true,
  },
  relations: {
    genre: true,
  },
});

Resulting the following error:

query failed: SELECT DISTINCT FROM ( SELECT `Movie`.`id` AS `Movie_id`, `Movie`.`languageCode` AS `Movie_languageCode`, `Movie`.`releaseDate` AS `Movie_releaseDate`, `Movie`.`length` AS `Movie_length`, `Movie`.`title` AS `Movie_title`, `Movie`.`storyline` AS `Movie_storyline`, `Movie`.`posterUrl` AS `Movie_posterUrl`, `Movie`.`trailerUrl` AS `Movie_trailerUrl`, `Movie`.`genreId` AS `Movie_genreId`, `Movie_genre`.`id` AS `Movie_genre_id`, `Movie_genre`.`nameMultilingual` AS `Movie_genre_nameMultilingual` FROM `movie` `Movie` LEFT JOIN `genre` `Movie_genre` ON `Movie_genre`.`id` = `Movie`.`genreId` WHERE ( (`Movie`.`languageCode` = ?) ) ) `distinctAlias` LIMIT 5 -- PARAMETERS: ["en"]

error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT `Movie`.`id` AS `Movie_id`, `Movie`.`languageCode` AS `Movie_langua' at line 1

Answer 1

The issue seems like the ViewEntity doesn't have a primary column to apply the distinct on. I solved the issue by adding a PrimaryColumn decorator to the id property on the ViewEntity.

@ViewEntity({
  expression: (dataSource: DataSource) =>
    dataSource
      .createQueryBuilder()

      .select('movie.id', 'id')
      .addSelect('movie.releaseDate', 'releaseDate')
      .addSelect('movie.length', 'length')
      .addSelect('movieTranslation.languageCode', 'languageCode')
      .addSelect('movieTranslation.title', 'title')
      .addSelect('movieTranslation.storyline', 'storyline')
      .addSelect('movieTranslation.posterUrl', 'posterUrl')
      .addSelect('movieTranslation.trailerUrl', 'trailerUrl')
      .addSelect('movie.genre', 'genreId')

      .from(MovieMetadata, 'movie')
      .leftJoin(MovieTranslation, 'movieTranslation', 'movieTranslation.movieId = movie.id'),
})
export class Movie {
  static readonly DEFAULT_LANGUAGE_CODE = 'en';
  static readonly STORYLINE_MAX_LENGTH = 765;

  @ViewColumn()
  @PrimaryColumn()
  id: number;

  @ViewColumn()
  languageCode: string;

  @ViewColumn()
  releaseDate: Date;

  @ViewColumn()
  length: number;

  @ViewColumn()
  title: string;

  @ViewColumn()
  storyline: string;

  @ViewColumn()
  posterUrl: string;

  @ViewColumn()
  trailerUrl: string;

  @ManyToOne(() => Genre, (genre) => genre.movies)
  @JoinColumn({ name: 'genreId' })
  genre: Genre;
}