SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE IF NOT EXISTS posts ( "id" serial, "title" varchar(255) NOT NULL, PRIMARY KEY ("id") ); CREATE TABLE IF NOT EXISTS comments ( "comment_id" serial, "comment_post_id" integer NOT NULL, PRIMARY KEY ("comment_id") ); CREATE INDEX ON comments (comment_post_id); CREATE TYPE entity_type AS ENUM ('comment', 'post', 'message', 'notification'); CREATE TABLE IF NOT EXISTS counters ( counter_id serial CONSTRAINT counters_pk PRIMARY KEY, user_id INTEGER NOT NULL, entity_id INTEGER NOT NULL, entity_type entity_type NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX ON counters (user_id); -- Insert new Post INSERT INTO posts (title) VALUES ('11'); -- Insert 2 comments for Post 1 INSERT INTO comments (comment_post_id) VALUES (1), (1); -- mark Comment 1 as readed INSERT INTO counters (user_id, entity_id, "entity_type") VALUES(1, 1, 'comment'); -- explain SELECT p.id post_id, MAX(comment_id) last_comment_id, COUNT(comment_id) total_count, COUNT(comment_id) - COUNT(entity_id) count_unread FROM posts p LEFT JOIN comments c ON p.id = c.comment_post_id LEFT JOIN counters AS cnt ON (cnt.entity_id = c.comment_id AND cnt.entity_type = 'comment') GROUP BY p.id ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear