How to union two tables with different structure in typeorm?

Question

I have two tablse with different structure: contents folders

I need getting data from two tables with pagination, sorting and filter. I know only about this way like getting two tables in 2 operations. Is it possible union these tables and making pagination, filter, sort to it in one operation?

    const contents: Content[] = await this.contentRepository.createQueryBuilder('content')
                .orderBy("content.id", "ASC")
                .skip(skip)
                .take(count)
                .getMany()
    const folders: Folder[] = await this.folderRepository.createQueryBuilder('folder')
  .orderBy("folder.id", "ASC")
                .skip(skip)
                .take(count)
                .getMany()

Answer 1

SELECT * FROM (
  SELECT 'content' as type, id, column1, column2, created_at FROM contents
  UNION ALL
  SELECT 'folder' as type, id, column1, column2, created_at FROM folders
) as union_table
WHERE <filter_conditions>
ORDER BY <sort_order>
OFFSET <skip> ROWS
FETCH NEXT <count> ROWS ONLY;

1. the 2 tables contents and folders are first selected individually using subqueries(an additional column type added to distinguish between them)

2. The results of the subqueries are then combined using the UNION ALL

3. resulting table is then filtered using <filter_conditions> in WHERE -- sorted according to <sort_order> in ORDER BY -- limited to a specific number of rows using OFFSET and FETCH NEXT

Ps. execute this query using database's query builder or by using a raw SQL query in your code --edit query according to your needs