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?

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 = 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