SQL Query用于连接包含多行的多个表 - 正确的方法?

I have a situation where I have 4 tables of following schema :

#1 : Table USER
user_id | user_details....

#2 : Table POSTS
post_id | user_id | post_details....

#3 : Table REPLIES
reply_id | post_id | by_user_id | reply_details...

#4 : Tables REPLY_PHOTOS
photo_id | reply_id | photo_details...

I want to retrieve all the posts of a particular user along with the replies on them and the photos in the replies. Every usage of columns from the previous table is in foreign key constraint.

SELECT USER.user_id, POST.post_id, REPLIES.reply_id, REPLY_PHOTOS.photo_id
FROM USER WHERE USER.user_id = 1
LEFT JOIN POST on USER.user_id = POST.user_id
LEFT JOIN REPLIES on POST.post_id = REPLIES.post_id
LEFT JOIN REPLY_PHOTOS on REPLIES.reply_id = REPLY_PHOTOS.reply_id;

The problem I'm facing is, when I am using joins normally I am getting rows that I suspect is not good practice.

Example of result I'm getting for user_id = 1 :

user_id | post_id | reply_id | photo_id
--------Post 1 - Reply 1 (Repeat for photos)----------------
   1         23        17         26
   1         23        17         32
   1         23        17         47
-------Post 1 - Reply 2 (Repeat for its photos)-----------
   1         23        34         12
   1         23        34         18
   1         23        34         23
   1         23        34         31
------Post 1 - Reply 3 (Contains no photos)------------
   1         23        41         null
---------Next Post for same user (contd.)----------------

As a user can have multiple posts, so multiple post_id's are related to a single user id. A post can contain multiple replies, so multiple reply_id's are related to a single post. A reply can contain multiple photos, so multiple photo_id's are related to a single reply.

Also, I want to further extend it to store Likes which will be another pain in the ass.

I don't know if my schema needs to be change (if yes, suggest me a better way) or if I'm not able to understand the basics of join.

If this is the way results should be, then how can I traverse to all the fields in php without much issues?

Thank you in advance.

查看全部
duanlushen8940
duanlushen8940
2014/03/29 21:13
  • php
  • mysql
  • join
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复