Select the first record in a one to many relation using left join-CodePudding

I'm trying to join two tables using a left-join. And the result set has to include only the first record from the "right" joined table.

Let's say I have two tables products and product_translations as below;

Table 'products'

id	price
1	100
2	150

Table 'product_translations'

id	product_id	lang	column	value
1	1	en	title	Product 1 Title
2	2	en	title	Product 2 Title
3	1	en	description	Product 1 Description
4	2	en	description	Product 2 Description
5	1	de	title	Produkt 1 Titel
6	2	de	title	Produkt 2 Titel
7	1	de	description	Produkt 1 Beschreibung
8	2	de	description	Produkt 2 Beschreibung

Expected output

id	price	title	description
1	100	Product 1 Title	Product 1 Description
2	150	Product 2 Title	Product 2 Description

CodePudding user response：

It seems you don't want the "first" row, but just two different ones based on the language. So you need to joins to the translation table: one for the "title" and one for the "description"

select p.*, t.description as title, d.description
from product p
  left join product_translations t 
         on t.product_id = p.id 
        and t.language = 'en'
        and t.column = 'title'
  left join product_translations d 
         on d.product_id = p.id 
        and d.language = 'en'
        and d.column = 'description'

If you want to retrieve this with a "primary" and "fallback" language, you can do something like this:

with product_texts as (
  select t.product_id, t.value as title, d.value as description, t.lang
  from product_translations t
    join product_translations d 
      on d.product_id = t.product_id
     and d.lang = t.lang
     and d."column" = 'description'
  where t."column" = 'title'   
    and t.product_id = 3
    and t.lang in ('de', 'en')
) 
select t.*
from product_texts t
order by case 
           when t.lang = 'de' then 1
           else 2
         end
limit 1

As it is a bit complicated to always join against that, you can create a function that does this:

create function get_translations(p_product_id int, p_lang text, p_fallback_lang text default 'en')
  returns table (product_id int, title text, description text, lang text)
as
$$ 
  with product_texts as (
    select t.product_id, t.value as title, d.value as description, t.lang
    from product_translations t
      join product_translations d 
        on d.product_id = t.product_id
       and d.lang = t.lang
       and d."column" = 'description'
    where t."column" = 'title'   
      and t.product_id = 3
      and t.lang in (p_lang, p_fallback_lang)
  ) 
  select t.*
  from product_texts t
  order by case 
             when t.lang = p_lang then 1
             else 2
           end
  limit 1
$$
language sql
rows 1
stable;

Then you can do:

select *
from products p
  left join lateral get_translations(p.id, 'de', 'en') on true

There is next to none overhead in calling the function as this will be inlined - just as if you had written the function's query into the main query.

For good performance you want an index on (product_id, lang, "column") or maybe even two filtered indexes:

create index product_lang_title 
  on product_translations (product_id, lang)
where "column" = 'title';

create index product_lang_descr 
   on product_translations (product_id, lang)
where "column" = 'description';

Online example

CodePudding user response：

Use a row_number to define the first row. I've assumed you mean "first record" is the one with the lowest "id" value.

with prod_t as
(
  select t1.*, row_number() over (partition by product_id, t1.column order by id) as rn
  from product_translations t1
)
select p1.*, t2.value as title, t3.value as desctiption
from products p1
left join prod_t t2
  on t2.product_id = p1.id
  and t2.column = 'title'
  and rn =1
left join prod_t t3
  on t3.product_id = p1.id
  and t3.column = 'description'
  and rn =1

Also, it is best to avoid using "column" as a column name