質問用に簡単なSQLフィドルを用意しました。
PostgreSQL 10には、ユーザーIDとユーザーソーシャルネットワークデータ(名前、写真など)を保持する2つのテーブルがあります。
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY
);
CREATE TABLE words_social (
sid text NOT NULL,
social integer NOT NULL CHECK (0 < social AND social <= 64),
given text NOT NULL CHECK (given ~ '\S'),
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
そして、ゲームとチャットメッセージを保持する2つのテーブルがあります:
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE
);
CREATE TABLE words_chat (
cid BIGSERIAL PRIMARY KEY,
created timestamptz NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg text NOT NULL
);
ここで、テーブルにテストデータを入力します。
SQL:
INSERT INTO words_users (uid) VALUES (1), (2);
INSERT INTO words_games (gid, player1, player2) VALUES (100, 1, 2);
INSERT INTO words_social (sid, social, given, uid) VALUES
('1111', 10, 'Alice', 1),
('2222', 20, 'Bob', 2);
INSERT INTO words_chat (created, gid, uid, msg) VALUES
(now() + interval '1 min', 100, 2, 'Hello, Alice'),
(now() + interval '2 min', 100, 1, 'Hello, Bob'),
(now() + interval '3 min', 100, 2, 'Nice to see you, Alice'),
(now() + interval '4 min', 100, 1, 'Nice to see you too, Bob'),
(now() + interval '5 min', 100, 2, 'Goodbye, Alice'),
(now() + interval '6 min', 100, 1, 'Goodbye, Bob');
私はPHPスクリプトを作成しようとしています。このスクリプトは、sid、social、およびgidが与えられると、ゲームgidの完全なチャットを返します。
他のユーザーがプレイしていないゲームをスパイしたくないので、チャットをフェッチして表示するためだけにgidパラメーターを使用するのではありません。
また、uidパラメーターを使用してスクリプトを呼び出すユーザーを識別することはできません。代わりにsidパラメーターとsocialパラメーターを使用する必要があります(さらに、上記のテストケースから省略したシークレット)。
チャットをフェッチするためのSQLステートメントは次のとおりです。
SELECT uid, msg
FROM words_chat
WHERE gid=100
AND EXISTS (select 1 from words_games where
(select uid from words_social
where sid='1111' and social=10) in (player1, player2))
ORDER BY CREATED ASC;
それは機能しますが、もう1つ必要なため、複雑になります。チャットメッセージごとに、「アリス」によるメッセージかどうかを知るために(uidではなく)ブール値が必要です(その線を引くことができます)。太字)。
だから私はこれをやろうとしていますが、SQLステートメントはさらに醜くなります:
SELECT uid=(select uid from words_social
where sid='1111' and social=10) AS mine, msg
FROM words_chat
WHERE gid=100
AND EXISTS (select 1 from words_games where
(select uid from words_social
where sid='1111' and social=10) in (player1, player2))
ORDER BY CREATED ASC;
上のスクリーンショットでわかるように、これは機能しますが、このクエリを改善するにはどうすればよいですか?
SQLに2番目のサブクエリでuidを再利用する方法はありますか(つまり、PHPスクリプトにuid値を保存したくない、またはpl / pgSQLを使用したくない)?
これは、join
sの使用について考えるのが非常に簡単です。
select wc.*, (wc.uid = ws.uid) as mine
from words_chat wc join
words_games wg
on wc.gid = wg.gid join
words_social ws
on ws.uid in (wg.player1, player2)
where wc.gid = 100 and
ws.sid = '1111' and
ws.social = 10
order by created asc;
これがSQLフィドルです。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加