How to write SELECT for translation of product?

there is a table product and product_translate

for one product there are translations to several languages

we need to choose a language that better understands the user (it has id) if such transfer is not, then select any other

this all in one query

how to write?

product_translate:
5cf7a26aab2aa671083287.png
it is possible to use subqueries, but subquery have 2 to write, 1 for title, 2 for description

and it is not clear how to write ORDER BY in subqueries
March 23rd 20 at 19:11
1 answer
March 23rd 20 at 19:13
select ...
from product as p
join lateral (
select title, description from product_translate as pt
where pt.product_id = p.id order by language_id = ? limit 1
) on true
where ....


As usual could make a mess sorting direction - append desc if the translation is, but it is not.

The idea is to choose any other language in the absence of the right - strange. But the query I wrote just that.
If no translations at all - now such goods will not appear. If you need a NULL instead of the text - join to replace on left join.

Find more questions by tags SQLPostgreSQL