Skip to main content
Version: 5.9

Virtual Entities

Virtual entities don't represent any database table. Instead, they dynamically resolve to an SQL query (or an aggregation in mongo), allowing to map any kind of results onto an entity. Such entities are mean for read purposes, they don't have a primary key and therefore cannot be tracked for changes. In a sense they are similar to (currently unsupported) database views.

To define a virtual entity, provide an expression, either as a string (SQL query):

We need to use the virtual column names based on current naming strategy. Note the authorName property being represented as author_name column.

./entities/BookWithAuthor.ts
@Entity({
  expression: 'select name, age, ' +
    '(select count(*) from book b where b.author_id = a.id) as total_books, ' +
    '(select group_concat(distinct t.name) from book b ' +
      'join tags_ordered bt on bt.book_id = b.id ' +
      'join book_tag t on t.id = bt.book_tag_id ' +
      'where b.author_id = a.id ' +
      'group by b.author_id) as used_tags ' +
    'from author a group by a.id',
})
export class BookWithAuthor {

  @Property()
  title!: string;

  @Property()
  authorName!: string;

  @Property()
  tags!: string[];

}

Or as a callback:

./entities/BookWithAuthor.ts
@Entity({
  expression: (em: EntityManager) => {
    return em.createQueryBuilder(Book, 'b')
      .select(['b.title', 'a.name as author_name', 'group_concat(t.name) as tags'])
      .join('b.author', 'a')
      .join('b.tags', 't')
      .groupBy('b.id');
  },
})
export class BookWithAuthor {

  @Property()
  title!: string;

  @Property()
  authorName!: string;

  @Property()
  tags!: string[];

}

In MongoDB, we can use aggregations, although it is not very ergonomic due to their nature. Following example is a rough equivalent of the previous SQL ones.

The where query as well as the options like orderBy, limit and offset needs to be explicitly handled in your pipeline.

@Entity({
  expression: (em: EntityManager, where, options) => {
    const $sort = { ...options.orderBy } as Dictionary;
    $sort._id = 1;
    const pipeline: Dictionary[] = [
      { $project: { _id: 0, title: 1, author: 1 } },
      { $sort },
      { $match: where ?? {} },
      { $lookup: { from: 'author', localField: 'author', foreignField: '_id', as: 'author', pipeline: [{ $project: { name: 1 } }] } },
      { $unwind: '$author' },
      { $set: { authorName: '$author.name' } },
      { $unset: ['author'] },
    ];

    if (options.offset != null) {
      pipeline.push({ $skip: options.offset });
    }

    if (options.limit != null) {
      pipeline.push({ $limit: options.limit });
    }

    return em.aggregate(Book, pipeline);
  },
})
export class BookWithAuthor {

  @Property()
  title!: string;

  @Property()
  authorName!: string;

}