SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE SEQUENCE IF NOT EXISTS posts_id_seq; CREATE TABLE IF NOT EXISTS posts ( "id" integer NOT NULL DEFAULT nextval('posts_id_seq'::regclass), "title" varchar(255) NOT NULL, PRIMARY KEY ("id") ); CREATE SEQUENCE IF NOT EXISTS comments_id_seq; CREATE TABLE IF NOT EXISTS comments ( "comment_id" integer NOT NULL DEFAULT nextval('comments_id_seq'::regclass), "comment_post_id" integer NOT NULL, PRIMARY KEY ("comment_id") ); CREATE INDEX ON comments (comment_post_id); CREATE SEQUENCE IF NOT EXISTS counters_id_seq; CREATE TYPE entity_type AS ENUM ('comment', 'post', 'message', 'notification'); CREATE TABLE IF NOT EXISTS counters ( counter_id INTEGER NOT NULL DEFAULT nextval ( 'counters_id_seq'::regclass ) 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 INTO posts (title) VALUES('11'); INSERT INTO comments (comment_post_id) VALUES(1); SELECT distinct(cnt.last_comment_id), cnt.count_unread, count(distinct(c.comment_id)) as total_count FROM comments AS c CROSS JOIN LATERAL ( SELECT distinct(c.comment_id) as last_comment_id, SUM(COUNT(distinct(c.comment_id))) OVER() as count_unread FROM posts AS p LEFT JOIN comments AS c ON p.id = c.comment_post_id WHERE (p.id IN (1) AND NOT EXISTS ( SELECT 1 FROM counters AS cnt WHERE cnt.entity_id = c.comment_id AND cnt.entity_type = 'comment' ) ) GROUP BY c.comment_id ORDER BY c.comment_id DESC LIMIT 1 ) as cnt GROUP BY cnt.last_comment_id, cnt.count_unread
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear