TypeORM: mapping unrelated entities into an array property

Question

I have the following unrelated TypeORM entities (for various reasons, I cannot use One/Many relationships on them):

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

   @Column()
   name: string;
}

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

   @Column()
   bookId: number;

   @Column()
   name: string;
}

With a single book, two assigned categories and the following query:

const query = this.bookRepository
      .createQueryBuilder('book')
      .leftJoinAndSelect(BookCategory, 'category', 'book.id = category.bookId');

return query.execute();

I get the following response:

[
  {
    "book_id": 1,
    "book_name": "Lorem Ipsum",
    "category_id": 1,
    "category_name": "non-fiction"
  },
  {
    "book_id": 1,
    "book_name": "Lorem Ipsum",
    "category_id": 2,
    "category_name": "documentary"
  }
]

As you see, I get 2 objects. Is there any way I can get the following with a TypeORM query?

[
  {
    "id": 1,
    "name": "Lorem Ipsum",
    "categories": ["non-fiction", "documentary"]
  }
]

I.e. I'm trying to bind a query result to a virtual property categories. Is that possible?

score 1 · Answer 1 · answered Dec 13 '19 at 11:08

I don't think this is possible with TypeORM itself, but you could achieve it using map() function in the next way:

const categories: string[] = query.execute().map((book) => book.category_name );
const book = {id: bookArray[0].book_id, name: bookArray[0].book_name, categories};

//OUTPUT: 
{
  id: 1,
  name: 'Lorem Ipsum',
  categories: [ 'non-fiction', 'documentary' ]
}

It's not the cleanist way to do it, but since you are requesting a single book by its ID you will be sure that the book_id and book_name properties are always the same in every array object.

Also TypeORM recommends using getMany() or getOne() instead of executing the query:

const books: Book[] = await this.bookRepository
  .createQueryBuilder('book')
  .leftJoinAndSelect(BookCategory, 'category', 'book.id = category.bookId')
  .getMany();

score 0 · Answer 2 · answered Feb 15 '21 at 20:25

I'm don't known how do his with queryBuilder, by on sql it would look something like this.

  SELECT
    book.id as book_id,
    book.name as book_name,
    array_remove(array_agg(bc.name), NULL) as categories
  FROM book
  LEFT JOIN book_category bc ON bc."bookId"=book.id
  GROUP BY book.id

I could have made a mistake because I did not check it myself. But it should be something like this.

When executed, you should get a table

book_id	book_name	categories
1	bookName1	['catName', 'catName2']
2	bookName2	['catName']

The main thing here is the array_agg and GROUP BY method. GROUP BY groups data by book.id, and array_agg groups it into an array

Method array_remove needed for remove NULL into array if category not found. If book always have category this method can be removed

TypeORM: mapping unrelated entities into an array property

2 Answers2

Linked