我试图为用户设计一个询问,以便检索到其他用户尚未与之互动的其他用户。
解释如下:
create schema if not exists bv;
create table if not exists bv.user (
id serial primary key,
user_name varchar(15) not null,
is_male boolean not null
);
create table if not exists bv.chat (
id serial primary key
);
create table if not exists bv.chat_message (
id serial primary key,
chat_id bigint references bv.chat (id),
user_id bigint references bv.user (id),
message_text varchar(255) not null
);
create table if not exists bv.junction_user_chat (
chat_id bigint references bv.chat (id),
user_id bigint references bv.user (id)
);
create table if not exists bv.user_coordinates (
id serial primary key,
user_id bigint references bv.user (id)
);
create table if not exists bv.user_like (
id serial primary key,
source_user_id bigint references bv.user (id),
target_user_id bigint references bv.user (id)
);
insert into bv.user values
(100, Mike , true),
(101, John , true),
(102, Jane , false),
(103, George , true),
(104, Lance , true);
insert into bv.chat values
(10);
insert into bv.junction_user_chat values
(10, 101),
(10, 102);
insert into bv.chat_message values
(500, 10, 101, hello ),
(501, 10, 102, how are you? );
insert into bv.user_coordinates values
(777, 100),
(778, 101),
(779, 102),
(780, 103),
(781, 104);
insert into bv.user_like values
(1, 101, 102),
(2, 102, 101),
(3, 100, 102),
(4, 103, 102),
(5, 102, 104);
让我假设:user with ID 102 (Jane)。 我期待我回来,
┌──────┬────────────────────┬──────────┐
│ id │ user_name │ is_male │
├──────┼────────────────────┼──────────┤
│ 100 │ Mike │ true │
│ 103 │ George │ true │
└──────┴────────────────────┴──────────┘
评论:
- Users 102 and 101 already have a chat (and chat messages) between them, which why user 101 is not returned in the query,
- User 102 does not exist as the
source_user_idinbv."user_like"— if they did exist in that table, then user 103 would not be returned in the query - Users 100 and 103 are returned because,
- they ve both "liked" user 102,
- user 102 not exist as
bv."user_like"."souce_user_id", - a chat doesn t exist for neither users
102 - 100nor102 - 103
- User 104 is not returned because user 102 already "liked" them (even though user 104 hasn t liked user 102 back at the moment)
同样,这里的想法是让所有用户都了解,目前的用户(约102人)已经与用户进行了互动。 我通过“互动”是指<代码>bv user_lies>、bv chat>或bv chat_message>上的记录。
我在此问:
SELECT DISTINCT ON (u.id)
u.*
FROM bv.user_coordinates uc
JOIN bv.user u
ON u.id = uc.user_id
JOIN (
SELECT
user_id,
chat_id
FROM bv.junction_user_chat
) chat_ids
ON chat_ids.user_id = 102
LEFT JOIN bv.user_like ul
ON u.id = ul.target_user_id
AND ul.source_user_id = 102
LEFT JOIN bv.chat_message cm
ON cm.chat_id = any(chat_ids)
AND (cm.user_id = u.id OR cm.user_id = 102)
WHERE u.id != 102
AND uc.user_id = u.id
AND u.is_male = true
AND ul.id is null
AND cm.id is null
GROUP BY u.id
ORDER BY u.id ASC
问题是,我不知道如何使用<条码>禁令_user_chat。 理想的情况是,我要形成一个阵列: ids(因为我已尝试这样做),然后检查用户代码<102<<>>>是否在场;如果是的话,我就想从结果中删除。
How can I get this to work, is my approach even in the correct direction? Postgres array methods and data types are new to me in general, I m much more used to junction tables, so I d much more prefer that over the current attempt with the nested join and ANY comparison.
最后,我要提到,这是实际查询的一个严重中下流的版本,因此,你看到了一个<条码>,从用户_coordinations<>/code>. 这一部分可以改变,但我在此保留。