My project involves a Postgres database that serves as a demo forum. Within this database, there are two tables:
topic
id | topic_id (the parent topic) | name | description
-------------------------------------------------------
uuid | uuid | text | text
and thread
id | topic_id | name | created_at
-------------------------------------------------------
uuid | uuid | text | timestamp without timezone
I am exploring options to retrieve all the information for a specific topic using an Express REST API. I would like the result to be structured like this:
{
parentTopic: {}, // could be null if no parent is available
name: "",
description: "",
childTopics: [{}, {}, {}],
threads: [{}, {}, {}] // should be sorted by created_at
}
I understand that returning a result in this exact format may not be straightforward. However, I have attempted to create a query that can extract this information, which I plan to further process with code later on.
SELECT
currentTopic.name,
currentTopic.description,
parentTopic.id AS parentTopicId,
parentTopic.name AS parentTopicName,
parentTopic.description AS parentTopicDescription,
childTopic.id AS childTopicId,
childTopic.name AS childTopicName,
childTopic.description AS childTopicDescription,
linkedThread.id AS threadId,
linkedThread.name AS threadName,
linkedThread.created_at AS threadCreatedAt
FROM
topic currentTopic
INNER JOIN
topic parentTopic ON currentTopic.topic_id = parentTopic.id
INNER JOIN
topic childTopic ON currentTopic.id = childTopic.topic_id
INNER JOIN
thread linkedThread ON currentTopic.id = linkedThread.topic_id
WHERE
currentTopic.id = '624aaab6-2d2d-45dc-a425-c2863f05779c'
ORDER BY
linkedThread.created_at;
While executing this query (using pgAdmin), I encountered no errors, but it returned 0 rows. I have verified that there are entries available to retrieve.
Could there be an issue with the query? Is there room for improvement to ensure the query returns a usable result structure (similar to the JS object mentioned earlier)?
Edit:
Below is some sample data from both the threads and topics tables:
Topic table:
https://i.sstatic.net/bv4Bo.png
Thread table: