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

Postgresql – Display Ancestral Relationships

I have a Postgresql database with the following tables and relations (fiddle):

page:    id, name

book:    id, page.id
chapter: id, page.id, book.id
section: id, page.id, chapter.id

I’d like to create a (materialized) view that is able to associate every page to the names of his "ancestors", for example, with the following data:

Books:

id pageid
1 1

Chapters:

id pageid bookid
2 2 1

Sections:

id pageid chapterid
3 3 2

Pages:

id name
1 foo
2 bar
3 baz

Should return something like this:

pageid bookname chaptername sectionname
1 foo null null
2 foo bar null
3 foo bar baz

I tried the following:

SELECT 
    book_page.name AS bookname,
    chapter_page.name as chaptername,
    section_page.name as sectionname
FROM (
    SELECT
          book.pageid AS bookpageid,
          chapter.pageid AS chapterpageid,
          section.pageid AS sectionpageid
        FROM book
          LEFT JOIN chapter ON chapter.bookid = book.id
          LEFT JOIN section ON section.chapterid = chapter.id
) AS hierarchy
LEFT JOIN page AS book_page    ON hierarchy.bookpageid    = book_page.id
LEFT JOIN page AS chapter_page ON hierarchy.chapterpageid = chapter_page.id
LEFT JOIN page AS section_page ON hierarchy.sectionpageid = section_page.id;

but I only get:

bookname chaptername sectionname
foo bar ba
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Ok, I might have figured it out:

    SELECT 
    page.id,
    book_page.name AS bookname,
    chapter_page.name as chaptername,
    section_page.name as sectionname
FROM page 
LEFT JOIN (
        (
            SELECT
                book.pageid AS bookpageid,
                chapter.pageid AS chapterpageid,
                section.pageid AS sectionpageid
            FROM book
                LEFT JOIN chapter ON chapter.bookid = book.id
                LEFT JOIN section ON section.chapterid = chapter.id
        )
        UNION
        (
            SELECT
                book.pageid AS bookpageid,
                chapter.pageid AS chapterpageid,
                Null as sectionpageid
            FROM book
                LEFT JOIN chapter ON chapter.bookid = book.id
        )
        UNION
        (
            SELECT
                book.pageid AS bookpageid,
                Null as chapterpageid,
                Null as sectionpageid
            FROM book
        )
) AS hierarchy ON
  (
    hierarchy.sectionpageid = page.id OR
    hierarchy.chapterpageid = page.id AND hierarchy.sectionpageid IS null OR
    hierarchy.bookpageid = page.id AND hierarchy.chapterpageid IS null
  )
LEFT JOIN page AS book_page    ON hierarchy.bookpageid    = book_page.id
LEFT JOIN page AS chapter_page ON hierarchy.chapterpageid = chapter_page.id
LEFT JOIN page AS section_page ON hierarchy.sectionpageid = section_page.id;

    Probably can be optimized, but for a materialized view I thinks it does the job


  2. 0

    Try this View

    CREATE MATERIALIZED VIEW page_hierarchy AS
SELECT
    p.id AS pageid,
    b.name AS bookname,
    c.name AS chaptername,
    s.name AS sectionname
FROM 
    page p
LEFT JOIN book b ON p.id = b.pageid
LEFT JOIN chapter c ON b.id = c.bookid
LEFT JOIN section s ON c.id = s.chapterid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search