Search code examples
sqlpostgresqlmany-to-many

Select many-to-many relation with "owns" column


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


Solution

  • 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'