选择用户既可以是朋友又可以是用户的行 [英] Selecting rows where user can be both friend and user
问题描述
嘿抱歉标题不是很清楚。我想在我的网页项目中设计一个表来存储友谊关系
我有以下朋友表
朋友(表名)
friend_id - 主键
user_id
user_id_friend
status
用户(表名)
user_id-主键
userName
-------------------------------------------------- ---------------------
状态例如(0表示拒绝,1表示已接受或2表示未处理
它至少应满足以下4个条件:
填充表的方式是 - 当我向John发送朋友请求时 - 我的userID出现在user_id中Johns userID出现在user_id_friend中。
现在另一种情况是Mike向我发送了一个朋友请求 - 在这种情况下,mike'的userID将出现在user_id中,我的userID将出现出现在user_id_friend
所以要找到我所有的朋友 - 我需要运行一个查询来查找我的userID出现在user_id列和user_id_friend列中的位置
我现在要做的是 - 当我搜索用户说约翰时 - 我希望我网站上列出的所有用户Johns都显示他们是否是我的朋友的状态,如果他们不是 - 那么显示添加好友按钮。
如果你们有这方面的经验,欢迎任何建议
我目前的设计(我现在认为不好)
请建议我使用sql来检索数据。
thanx提前。
我会说你说的具体需求,
你只能用一张桌子并且有一个布尔字段 IsFriend 。
即使您添加更多功能,因此更多字段朋友实体,你仍然可以拥有USER表hol d朋友和FriendSpec的另一个表格,即朋友的其他属性,是普通用户的额外属性。
干杯,
Edo
Hey Sorry the title is not very clear.I want to design a table to store friendship relationship in my web project
I have a following friends Table
Friend(Table Name)
friend_id - primary key
user_id
user_id_friend
status
User(Table Name)
user_id- primary key
userName
-----------------------------------------------------------------------
status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed
It should satisfy at least the following 4 conditions:
The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Johns userID appears in user_id_friend.
Now another scenario is say Mike sends me a friend request - in this case mike''s userID will appear in user_id and my userID will appear in user_id_friend
So to find all my friends - I need to run a query to find where my userID appears in both user_id column as well as user_id_friend column
What I am trying to do now is - when I search for user say John - I want all users Johns listed on my site to show up along with the status of whether they are my friend or not and if they are not - then show a Add Friend button.
If any of you are experienced with this , any suggestion is welcomed
my current design (I think bad right now)
please suggest me a sql to retrieve the data.
thanx in advance.
I''d say for the specific needs you stated,
you can use only one table and have a boolean field of IsFriend.
Even if you add more functionality and thus more fields to the Friend entity, you can still have the USER table hold friends and another table for FriendSpec i.e. other attributes of Friend that are additional to a regular User.
Cheers,
Edo
这篇关于选择用户既可以是朋友又可以是用户的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!