SQLize | PHPize | SQLtest

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); INSERT INTO comments (comment_post_id) VALUES(1); INSERT INTO counters (user_id, entity_id, "entity_type") VALUES(1, 1, 'comment'); 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