我想要一个具有以下列的表:id,user_id1和user_id2.
I want to have a table with columns: id, user_id1, and user_id2.
基本上,这将表示一个朋友图,其链接表示user1是user2的朋友,反之亦然.
basically this will resprsent a friend graph with the link representing that user1 is friends with user2 AND vice versa.
我的设置得到一个用户,然后是他们的朋友列表.鉴于我不想在表中有多余的条目,应该如何处理呢?
my setup gets a single user and then a list of their friends. given that I don't want to have extra entries in my table how shoudl I handle this?
我想做类似的事情:insert into friendship (user_id1, user_id2) values (<id1>, <id2>) where ...
但是我不确定如何执行SQL中的条件逻辑
but I'm not sure how to do conditional logic like that in SQL
推荐答案您可以通过CHECK约束强制user_id1始终小于user_id2:
You could force user_id1 to always be less than user_id2 with a CHECK constraint:
CHECK (user_id1 < user_id2)大概不允许人们成为自己的朋友.然后在插入之前确保ID顺序正确.提取朋友列表时,您仍然需要检查两列:
Presumably people aren't allowed to be their own friends. And then make sure the IDs are in the right order before you INSERT. When extracting a list of friends, you'd still have to check both columns though:
select user_id2 from friendship where user_id1 = X union all select user_id1 from friendship where user_id2 = XX当然是您感兴趣的人.要查看两个人是否是朋友,只需按正确的顺序排列其ID,然后选择SELECT.
where X is, of course, the person you're interested in. And to see if two people are friends, just arrange their IDs in the right order and SELECT away.
更多推荐
SQL构造双向图
发布评论