explaining postgres query I need to make a specific query because I can't seem to find any working solution
I am creating an app like this one
https://anonymous-message-board.freecodecamp.rocks[][1]
basically, the application consists of many boards and each board have a number of threads and each thread have a number of replies
what I need to do is when some user heads to a specific board (makes a get request) I need to send him the threads of that board and the replies of each thread
the replies should be an array within the thread knowing that the threads are sorted by the created_on and limited a to 10 threads also the replies are sorted with created_on and limited to 3 results
here is :
the database tables
CREATE TABLE threads (
    _id SERIAL NOT NULL PRIMARY KEY,
    board_id INT NOT NULL REFERENCES board(id),
    text VARCHAR(255) NOT NULL,
    delete_password VARCHAR(255) NOT NULL,
    reported  BOOLEAN NOT NULL,
    created_on DATE NOT NULL DEFAULT CURRENT_DATE,
    bumped_on DATE  DEFAULT CURRENT_DATE
);


CREATE TABLE board (
    _id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE replies (
   _id SERIAL PRIMARY KEY NOT NULL,
   thread_id INT NOT NULL REFERENCES threads(id),
   text VARCHAR(255) NOT NULL,
   delete_password VARCHAR(255) NOT NULL,
   reported  BOOLEAN NOT NULL,
   created_on DATE NOT NULL DEFAULT CURRENT_DATE
);

however, I tried doing a lot of queries without getting the desired result
my main obstacle was that I need to select the replies for each thread
I tried this :
but as you know here I can't select many columns from a subquery
  SELECT  id as _id, txt as text, delete_password, reported, created_on, bumped_on ,
 (
    select * from replies 
    where thread_id = thd.id
    order by created_on DESC
    limit 3 
 )  subquery   
 from threads thd where board_id = 1
 order by created_on DESC
 limit 10;


tried this also but didn't get the replies array as desired:
select id as _id, txt as text, delete_password, reported, created_on, bumped_on, array_agg(replies) as replies
(select count(*) replycount from replies group by thread_id)
A classical case for a lateral join:
SELECT thd.id as _id,
       thd.txt as text,
       thd.delete_password,
       thd.reported,
       thd.created_on,
       thd.bumped_on,
       r.*
FROM (SELECT *
      FROM threads
      WHERE board_id = 1
      ORDER BY created_on DESC
      LIMIT 10) AS thd
   LEFT JOIN LATERAL
      (SELECT * FROM replies
       WHERE thread_id = thd.id
       ORDER BY created_on DESC
       LIMIT 3) AS r ON TRUE;