Postgresql – Use twice DISTINCT ON ina multiple join situation

postgresqlpostgresql-10query

I'm using PostgreSQL 10. I have an entity Product.

The product has images, so I have a Foreign key from Product to Image.

A Product have 1 ON 1 relationship, with a Video entity

A product can have multiple categories and a category can have multiple products, so I have M2M relationship from Product to Category

--- Product ---
id | name | 

--- Image ---
id | image | product_id

--- Video ---
id | channel | url |product_id

--- CategoryProduct ---
id | category_id |product_id

--- Category ---
id | name

I need to get a list of Product(s) with their first/one Image and Video and also the first Category

I use DISTINCT ON to get just one image, in the query bellow:

SELECT DISTINCT ON(p.id)  p.id,  p.name, i.image, coalesce(v.channel, v.url) as video 
                  FROM product AS p
                  INNER JOIN  image as i on p.id = i.product_id 
                  LEFT JOIN video as v on p.id = v.product_id"

My issue is how do I get also the first Category for the Product ?

Best Answer

If I understand correctly a product can have more than one image/video an of course more than one category.

In this case you can use a LATERAL JOIN and limit the result to only one row.

I've set up one small fiddle just to show you:

select p.id,
       p.name,
       i.image,
       v.channel,
       v.url,
       c.name
from   products p
left join lateral (select id, image
                   from   image
                   where  product_id = p.id
                   limit  1) i on true
left join lateral (select id, channel, url
                   from video
                   where product_id = p.id
                   limit 1) v on true
left join lateral (select c.id, c.name 
                   from   categoryproduct cp
                   join   category c
                   on     cp.category_id = c.id
                   where  cp.product_id = p.id
                   limit 1) c on true;

id | name  | image | channel | url  | name
-: | :---- | :---- | :------ | :--- | :---
 1 | prod1 | img1  | c1      | url1 | cat1
 2 | prod2 | img3  | c3      | url3 | cat3

db<>fiddle here

Related Question