User Views Public Reading Lists

Authors: Regular User / Guest User

A user browses all public reading lists on the platform to discover new stories recommended by other readers.

1. The user navigates to the "Community Reading Lists" section.

2. The system retrieves all public reading lists along with their story counts.

SELECT rl.list_id, rl.name, rl.content, u.username,
       COUNT(rli.story_id) AS total_stories, rl.created_at
FROM READING_LIST rl
JOIN USERS u ON rl.user_id = u.user_id
LEFT JOIN READING_LIST_ITEMS rli ON rl.list_id = rli.list_id
WHERE rl.is_public = TRUE
GROUP BY rl.list_id, u.username
ORDER BY total_stories DESC;

3. The user selects a reading list to view its contents.

4. The system displays all stories in that reading list with their details.

SELECT s.story_id, s.short_description, s.mature_content,
       u.username AS author, COUNT(l.user_id) AS likes,
       rli.added_at
FROM READING_LIST_ITEMS rli
JOIN STORY s ON rli.story_id = s.story_id
JOIN USERS u ON s.user_id = u.user_id
LEFT JOIN LIKES l ON s.story_id = l.story_id
WHERE rli.list_id = 1
GROUP BY s.story_id, u.username, rli.added_at
ORDER BY rli.added_at DESC;

5. The user clicks on a story from the list and is redirected to the story page.