I put my efforts to solve following problem: I have 2 tables in my database: videos and users_videos as pivot between videos and users (i receive user_id from token, so users table is in another db)
Having an user id, i want to select all videos and attach column containing true or false whether user owns a video.
So far i realized this with following query:
SELECT v.*, TRUE AS has_video FROM users_videos AS uv
RIGHT JOIN videos AS v
ON uv.video_id = v.id
WHERE (uv.user_id = 1)
UNION
(
SELECT v.*, FALSE AS has_video FROM videos AS v
EXCEPT
SELECT v.*, FALSE AS has_video FROM users_videos AS uv
RIGHT JOIN videos AS v
ON uv.video_id = v.id
WHERE (uv.user_id = 1)
)
Although it selects all videos 3 times. Is there any more optimal solution for such kind of problems?
@EDIT ---
Tables structure
users_videos:
id integer
user_id integer
video_id integer
videos:
id: integer
title: string
Example data:
users_videos
| id | user_id | video_id
-------------------------
1 1 1
videos
| id | title |
----------------
1 | Example 1
-----------------
2 | Example 2
-----------------
Desired result:
| id | title | has_video
------------------------------
1 | Example 1 | true
------------------------------
2 | Example 2 | false
------------------------------
@UPDATE --
Used @Stefano Zanini approach:
SELECT DISTINCT
v.*,
CASE
WHEN uv.user_id IS NULL OR uv.user_id <> 1 THEN FALSE
ELSE TRUE
END has_video
FROM videos v
LEFT JOIN
users_videos uv
ON uv.video_id = v.id
But one more question came to my mind:
What if i want to display videos in specific category? Let's say it's another many to many relation on
videos and categories tables with pivot videos_categories
You can do that without the UNION
and EXCEPT
like this
select distinct
v.*,
case
when uv.user_id is null or uv.user_id <> 1 then false
else true
end has_video
from videos v
left join
user_videos uv
on uv.video_id = v.id
Since you described this as a many-to-many
relationship, more than one user can own the same video, hence the need for distinct
.
The swap from right
to left
join is just because I think it's easier to read.
Edit
To filter the results on a specific category (as per question edit) you can add a couple of join
and a condition in the where
select distinct
v.*,
case
when uv.user_id is null or uv.user_id <> 1 then false
else true
end has_video
from videos v
join videos_categories vc
on v.id = vc.video_id
join categories c
on vc.category_id = c.id
left join
user_videos uv
on uv.video_id = v.id
where c.category = 'someCategory'