forum unread topic

topic_unread.sql

create table user ( id serial, ... );
create table group ( id serial, ... );

create table topic (
    id serial,
    group_id int foreign key group(id)
);

create table lastread (
    id serial,
    user_id int foreign key user(id),
    topic_id int foreign key topic(id),
    last_post int
);


select 
  distinct t.group_id
from 
  topic t left join lastread lr on (t.id == lr.topic_id)
where
  (lr.topic_id is null) or 
  (t.last_post_id > lr.post_id and lr.user_id == ?)
;