Question 89247 in PostgreSQL Question posted in
The official documentation can be found here.

How to union two tables with different structure in typeorm? – Postgresql

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()
Tags:

2

Answers


  1. 0

    The actual table structure is irrelevant for union. It is the columns selected which determine the validity. There must be the same number of columns and each column have compatible data types. (See Documentation for Combining Queries). You can get data from different structured tables by supplying the column names from the tables. (You cannot use select *... – at least in both). See Demo here.
    Note: Column headers always correspond to column names or column aliases from the first select.

    Sorry, I do not know your obscurification manager (typeorm?). So I cannot convert, you will need to make that conversion, or just use raw sql.

    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search