douzao9845 2016-02-28 02:04 采纳率: 0%
浏览 77
已采纳

构造一个MySQL表,其中PLAYLIST表引用多个VIDEO id整数和一个USER表id

I want to retrieve one complete object from VIDEOPLAYLIST that contains the complete USER object and all the VIDEO objects related to it.

I understand that I will need to use LEFT JOIN to merge them, but I cant understand how i should setup my PLAYLIST table and how to query it when it comes to multiple VIDEO id's.

This is my current database EER Diagram: Original EER Diagram of my database tables

Should I create a many to many relationship between the playlist and video? Should I somehow store all the VIDEO id's in the PLAYLIST table? And then how would be able to query it?

This is where I'm stuck:

SELECT * FROM videoplaylist LEFT JOIN user LEFT JOIN video ON user.id = playlist.user_id AND videos....

PS. I won't just be querying one PLAYLIST at the time, as I need to display them in a list format.

  • 写回答

1条回答 默认 最新

  • dpvmjk0479 2016-02-28 02:12
    关注

    Yes, you should create a many-to-many relationship between the video and playlist table if you want that one video could be added to multiple playlists and vice versa.

    So your many-to-many relationship table could look like this where the combination of playlist_id and video_id is your primary key.

    playlist_has_video
    ------------------
    playlist_id
    video_id
    

    If you want to be able to add a video more than once to a playlist you have to create an id col in this table too. So now id is your primary key.

    playlist_has_video
    ------------------
    id
    playlist_id
    video_id
    

    When you want to enable the user to sort the playlist think about adding another col order_id to your n-to-m table.

    playlist_has_video
    ------------------
    id
    playlist_id
    video_id
    order_id
    

    Now lets see the select queries (i will refer to the last design, id + order_id)

    retrieving all videos from a playlist:

    SELECT video.* FROM playlist_has_video AS phv LEFT JOIN video ON phv.video_id = video.id WHERE phv.playlist_id = :playlist_id ORDER BY order_id ASC;
    

    Retrieving all playlists the video is in:

    SELECT playlist.* FROM playlist_has_video AS phv LEFT JOIN playlist ON phv.playlist_id = playlist.id WHERE phv.video_id = :video_id;
    

    You have to alter you video - table too if you use this approach (Make sure you remove playlist_id)

    video
    ---------
    id
    title
    description
    user_id
    transcript_id
    created
    updated
    

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 文本数据挖掘分析报告
  • ¥15 代写uni代码,app唤醒
  • ¥15 全志t113i启动qt应用程序提示internal error
  • ¥15 ensp可以看看嘛.
  • ¥80 51单片机C语言代码解决单片机为AT89C52是清翔单片机
  • ¥60 优博讯DT50高通安卓11系统刷完机自动进去fastboot模式
  • ¥15 minist数字识别
  • ¥15 在安装gym库的pygame时遇到问题,不知道如何解决
  • ¥20 uniapp中的webview 使用的是本地的vue页面,在模拟器上显示无法打开
  • ¥15 网上下载的3DMAX模型,不显示贴图怎么办
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部