0

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)

1 Answer 1

2

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;
3
  • 1
    Yes thank you but it seems that it only gets the thread if it has replies am I right I need something like an outer left join where it doesn't matter if the replies for a thread doesn't exist Oct 9, 2020 at 19:10
  • Do you think it will be better if Ii did with two queries. for example, make some trick in the backend. I use nodejs Oct 9, 2020 at 19:11
  • No, that was my mistake: I have modified my answer to use a left outer join. Oct 12, 2020 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.