我正在开发一个照片分享应用平台。这款应用允许你发布照片,其他人可以给照片点赞或打分。用户可以互相关注,看到他们的"关注者"分享的照片,就像instagram一样。
#user_tbl
id | name | number
-------------------
1 | Dan | 0209
2 | Sam | 2854
3 | Dave | 8123
4 | Alex | 5600
#photo_tbl
id | userid | path
-------------------
1 | 3 | dave-dog.jpg
2 | 1 | dans-cat.png
3 | 4 | alex-bird.jpg
4 | 2 | sam-fish.jpg
#friendship_tbl
id | actor | target
--------------------
1 | 2 | 1 // Sam is following Sam
2 | 2 | 4 // Sam is following Alex
3 | 1 | 3 // Dan is following Dave
4 | 4 | 2 // Alex is following Sam
#activities_stream_tbl
id | photoid | userid | context | date
----------------------------------------------------------
1 | 3 | 4 | add-new-photo | 10/10/2015
2 | 1 | 3 | add-new-photo | 12/10/2015
3 | 3 | 2 | Sam-share-Alex-photo | 15/10/2015
4 | 4 | 2 | add-new-photo | 20/10/2015
6 | 1 | 1 | Dan-like-Dave-photo | 21/10/2015
#user_table保存了用户的基本信息,而#photo_tbl
保存了用户共享的照片的名称和路径。#friendship_tbl
中是用户之间的关系链接。"actor"
列为执行以下操作的用户id, "target"
列为被关注的用户id。
我目前在编写查询字符串以提取USERX
的照片和其他用户USERX
的照片时遇到问题,并在actities_stream_tbl中按"photoid"分组,ORDER by"date"actities_stream_tbl。
我将很高兴,如果有人可以帮助我,告诉我一个更好的方式构建数据库,谢谢。
要获取USERX的照片,可以构造如下sql
select PATH
from user_tbl as a inner join photo_tbl as b
on a.id = b.user_id
and a.name = 'userx'
,并且要提取USERX正在关注的其他用户的照片,您可以写入
select path
from photo_tbl as a
where a.userid in (select target from friendship_tbl as x inner join user_tbl as y on x.actor = y.id and y.name = 'user')
如果你愿意,可以将以上两个结果合并。
,
select PATH
from user_tbl as a inner join photo_tbl as b
on a.id = b.user_id
and a.name = 'userx'
UNION
select path
from photo_tbl as a
where a.userid in (select target
from friendship_tbl as x
inner join user_tbl as y
on x.actor = y.id and y.name = 'user')