while user names and IP addresses are saved in the other database: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)
);
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.
