On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber < alexander.far...@gmail.com> wrote:
> Good evening, > > In a 9.5 database I would like players to rate each other and save the > reviews in the table: > > CREATE TABLE words_reviews ( > uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users > ON DELETE CASCADE, > author integer NOT NULL REFERENCES words_users(uid) ON DELETE > CASCADE, > nice integer NOT NULL CHECK (nice = 0 OR nice = 1), > review varchar(255), > updated timestamptz NOT NULL, > PRIMARY KEY(uid, author) > ); > > while user names and IP addresses are saved in the other database: > > CREATE TABLE words_users ( > uid SERIAL PRIMARY KEY, > ip inet NOT NULL, > .......... > ); > > [...] > all previous reviews coming from the same IP in the past 24 hours: > SELECT (uid, author) -- locate reviews FROM word_reviews JOIN words_users USING (u_id) WHERE u_id IN ( -- from each of the following users... SELECT wu.u_id FROM words_users wu WHERE wu.ip = (SELECT wui.ip FROM words_users wui WHERE wui,uid = in_uid) -- find all users sharing the ip address of this supplied user ) AND updated >= [...] -- but only within the specified time period David J.
