TypeORMError: alias was not found

Question

I'm trying to make API pagination for GET /authors. I have bidirectional many to many relation between authors and books table.

I found that problem is when using creatingQueryBuilder() in combination with .leftJoinAndSelect() and .skip() I get TypeORMError: ""authors"" alias was not found. Maybe you forgot to join it?. But I'm not sure how to solve it.

My database look like this:

library=# select * from authors;
 id | first_name | last_name | birth_date |         created_at         |         updated_at         
----+------------+-----------+------------+----------------------------+----------------------------

library=# select * from books;
 id |      title       |         isbn          | pages |         created_at         |         updated_at         
----+------------------+-----------------------+-------+----------------------------+----------------------------

library=# select * from books_authors 
 books_id | authors_id 
----------+------------
(4 rows)

Entities look like this:

import { Exclude } from 'class-transformer';
import { BookEntity } from 'src/book/entities/book.entity';
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  BeforeInsert,
  ManyToMany,
} from 'typeorm';

@Entity({ name: 'authors' })
export class AuthorEntity {
  @Exclude()
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column({ type: 'date', nullable: true })
  birthDate: Date | null;

  @Exclude()
  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @Exclude()
  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  updatedAt: Date;

  @ManyToMany(() => BookEntity, (book) => book.authors)
  books: BookEntity[];

  @BeforeInsert()
  updateTimestamp() {
    this.updatedAt = new Date();
  }
}

import { Exclude } from 'class-transformer';
import { AuthorEntity } from 'src/author/entities/author.entity';
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  BeforeInsert,
  ManyToMany,
  JoinTable,
} from 'typeorm';

@Entity({ name: 'books' })
export class BookEntity {
  @Exclude()
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  isbn: string;

  @Column()
  pages: number;

  @Exclude()
  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @Exclude()
  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  updatedAt: Date;

  @ManyToMany(() => AuthorEntity, (author) => author.books)
  @JoinTable({ name: 'books_authors' })
  authors: AuthorEntity[];

  @BeforeInsert()
  updateTimestamp() {
    this.updatedAt = new Date();
  }
}

Service method looks like this:

async findAll(
    pageOptionsDto: PageOptionsDto,
    filterAuthorDto: FilterAuthorDto,
  ): Promise<PageDto<AuthorEntity>> {
    const builder = this.dataSource
      .getRepository(AuthorEntity)
      .createQueryBuilder('authors');

    if (filterAuthorDto?.firstName) {
      builder.where('"authors"."first_name" LIKE :firstName', {
        firstName: `%${filterAuthorDto.firstName}%`,
      });
    }

    if (filterAuthorDto?.lastName) {
      builder.andWhere('"authors"."last_name" LIKE :lastName', {
        lastName: `%${filterAuthorDto.lastName}%`,
      });
    }
    
    // This part of code is problematic
    builder
      .innerJoinAndSelect('authors.books', 'books')
      .orderBy('"authors"."created_at"', pageOptionsDto.order)
      .skip(pageOptionsDto.skip)
      .take(pageOptionsDto.perPage);

    const total = await builder.getCount();
    const { entities } = await builder.getRawAndEntities();

    const pageMetaDto = new PageMetaDto({ total, pageOptionsDto });

    return new PageDto(entities, pageMetaDto);
  }

Answer 1

Just remove the double quotation inside the string, it's redundant and makes typeorm get confused and couldn't find related defined alias.

...
.orderBy('authors.created_at', pageOptionsDto.order)
...