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

Question

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:

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.

Answer 1

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