How to get nested relationship entities with nested subqueries SQL?-CodePudding

I have these 4 tables: Scorecard, Section, Topic, Answer.

Each scorecard can have many sections. Each Section can have many topics and each Topic can have many answers.

I am looking to retrieve the data in the following form. Get all sections where scorecard id is equal to to a certain id i do this by left joining and it works so far. Then for each section I want to get of all it's topics and answers as well.

=> Expected Output

[{section1, 
topics:[{topicName, answers:[{answerName}]}]}, 
{section2, 
topics:[{topicName, answers:[{answerName}]}]}]

I made adjustments to my query and joint the tables correctly but the output data is not correctly organized as I was It to be.

Query:

    SELECT sec."id",
       sec."name" AS "sectionName",
       to_json(topics) as "topics"
       
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN  (SELECT tp.*, to_json(answers.*) as "answers"
            FROM "topic" tp
            LEFT JOIN (SELECT ans."name" as "answerName", ans."topicId"
            FROM "answer" ans) answers ON answers."topicId" = tp."id"
            ) topics
            ON topics."sectionId" = sec."id"
WHERE sc."id" = $1

The current output:

    [  id: 85,
      sectionName: 'Consultation',
      topics: {
        id: 109,
        name: 'Was the information correct?',
        answers: [{ answerName: 'Yes', topicId: 109 },        answers: { answerName: 'NO', topicId: 109 }
]
      },
      id: 85,
      sectionName: 'Consultation',
      topics: {
        id: 109,
        name: 'Was the information correct?',
        answers: { answerName: 'NO', topicId: 109 }
      }]

My expected output:

[  {id: 85,
  sectionName: 'Consultation',
  topics: [{
    id: 109,
    name: 'Was the information correct?',
    answers: [{ answerName: 'Yes', topicId: 109 },
             { answerName: 'NO', topicId: 109 }]
  }]
} ]

I have also tried JOINING all entites as such:

SELECT sec."id",
       sec."name" AS "sectionName",
       tp."name" AS topics, ans."name" AS answers
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN "topic" tp ON tp."sectionId" = sec."id"
LEFT JOIN "answer" ans ON ans."topicId"= tp."id"
WHERE sc."id" = $1

Output is not organized as it should be.

[
 {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'No'
  },
  {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'Not everytime'
  },
  {
    id: 91,
    sectionName: 'Politeness',
    topics: 'Was the agent polite with the client?',
    answers: 'Yes'
  }
]

CodePudding user response：

You can join all four tables to get your result.

SELECT sec."id",
       sec."name" AS "sectionName",
       tp."name" AS topics, ans."name" AS answers
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN "topic" tp on tp."sectionId" = sec."id"
LEFT JOIN "answer" ans on ans."topicId"=tp."id"
WHERE sc."id" = 30

Thanks @Luuk for creating the dbfiddle link.

  select json_agg(x) AS result
  from 
  (
    SELECT sec."id",
        sec."name" AS "sectionName",
        json_agg(topics) as "topics"
        
   FROM "scorecard" sc 
   LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
   LEFT JOIN  (SELECT tp.id,tp.name,tp."sectionId", json_agg(answers.*) as "answers"
             FROM "topic" tp
             LEFT JOIN (SELECT ans."name" as "answerName", ans."topicId"
             FROM "answer" ans) answers ON answers."topicId" = tp."id"
             group by tp.id,tp.name,tp."sectionId"
             ) topics
             ON topics."sectionId" = sec."id"
   WHERE sc."id" = 85
   group by sec."id", sec."name"
 )x

Output:

result
[{"id":85,"sectionName":"Consulation","topics":[{"id":109,"name":"Was the information correct?","sectionId":85,"answers":[{"answerName":"Yes","topicId":109}, <br> {"answerName":"NO","topicId":109}]}]}]

*db<>fiddle here149)

CodePudding user response：

You are missing a JOIN condition to the table topics

SELECT sec."id",
       sec."name" AS "sectionName",
       topics
FROM "scorecard" sc 
LEFT JOIN "section" sec ON sc."id" = sec."scorecardId"
LEFT JOIN (SELECT tp.* 
      FROM "topic" tp 
      WHERE tp."sectionId" = sec."id"
           (SELECT tp.* 
            FROM "topic" tp 
            WHERE tp."sectionId" = sec."id"
           )
      ) as topics  ON topics.??? = ???  -- Here you should enter the relation
                                        --  from topics to your section.
WHERE sc."id" = 30

EDIT:

With you (current) input I creates this DBFIDDLE

The output:

[
    {
        "id": 85,
        "sectionName": "Consulation",
        "topics": {
            "id": 109,
            "name": "Was the information correct?",
            "sectionId": 85,
            "answers": {
                "answerName": "Yes",
                "topicId": 109
            }
        }
    },
    {
        "id": 85,
        "sectionName": "Consulation",
        "topics": {
            "id": 109,
            "name": "Was the information correct?",
            "sectionId": 85,
            "answers": {
                "answerName": "NO",
                "topicId": 109
            }
        }
    }
]